vlookupwtf

Computers, gaming, gadgets and geekery.

vlookupwtf

Post by deev @ Sun May 06, 2018 8:27 pm

What am I doing wrong here? This is OpenOffice so a bit different to excel but I'm trying to do a vlookup thingmy. in my wee box where my arrow points I want to be able to type in a persons name and it have their sales data for that particular quarter appear. It works for some peope, ie "Craig" but it stops working properly as I go down the list, throwing up errors or results that correlate to someone else.

ie I type in "Robyn" into the q1 query box and get Craigs q1 sales.

vlookup.png
vlookup.png (115.72 KiB) Viewed 545 times
User avatar
deev
Banger King
 
Posts: 14679
Joined: Mon Oct 22, 2007 8:14 pm
Location: Giffnock
Highscores: 3

Re: vlookupwtf

Post by mmm-five @ Sun May 06, 2018 8:43 pm

It’s because your range is dynamic, and so the reference range moves when you copy the formula down.

In Excel, you’d normally put $ in front of each of the range references (e.g. A1:B11 becomes $A$1:$B$11] to make it absolute.
Last edited by mmm-five on Sun May 06, 2018 8:45 pm, edited 1 time in total.
mmm-five
 
Posts: 749
Joined: Thu Apr 03, 2008 11:22 am

Re: vlookupwtf

Post by deev @ Sun May 06, 2018 8:59 pm

I tried that too, it still wont search after Craig and Ross.Totally stumped.
User avatar
deev
Banger King
 
Posts: 14679
Joined: Mon Oct 22, 2007 8:14 pm
Location: Giffnock
Highscores: 3

Re: vlookupwtf

Post by Disastrous @ Sun May 06, 2018 9:42 pm

No idea, but Robert seems to be swinging the leg a bit!
User avatar
Disastrous
Monkey Licker!
 
Posts: 39121
Joined: Thu Feb 28, 2008 3:58 pm

Re: vlookupwtf

Post by Tokyo Sexwale @ Mon May 07, 2018 6:56 am

Disastrous wrote:swinging the leg

Typo?
User avatar
Tokyo Sexwale
 
Posts: 48836
Joined: Thu Jan 24, 2008 5:14 pm

Re: vlookupwtf

Post by Disastrous @ Mon May 07, 2018 7:33 am

Tokyo Sexwale wrote:
Disastrous wrote:swinging the leg

Typo?



It means ‘being lazy’ - his numbers seem well down!

Is it a Scottish saying maybe?
User avatar
Disastrous
Monkey Licker!
 
Posts: 39121
Joined: Thu Feb 28, 2008 3:58 pm

Re: vlookupwtf

Post by morebeanz @ Mon May 07, 2018 7:41 am

The list you are looking up from needs to be in sequential order, if I recall correctly - in this case alphabetic...


Spent from masturbating with my iprick using Tapatalk
User avatar
morebeanz
 
Posts: 3407
Joined: Thu Feb 28, 2008 3:35 pm
Location: Surrey
Highscores: 2

Re: vlookupwtf

Post by Damien Thorn @ Mon May 07, 2018 8:13 am

Disastrous wrote:
Tokyo Sexwale wrote:
Disastrous wrote:swinging the leg

Typo?



It means ‘being lazy’ - his numbers seem well down!

Is it a Scottish saying maybe?
No, "swinging the lead" means being lazy.
Last edited by Damien Thorn on Mon May 07, 2018 8:16 am, edited 1 time in total.
User avatar
Damien Thorn
Spamcop
Spamcop
 
Posts: 12722
Joined: Mon Oct 06, 2008 10:24 am

Re: vlookupwtf

Post by Tokyo Sexwale @ Mon May 07, 2018 9:28 am

Disastrous wrote:
Tokyo Sexwale wrote:
Disastrous wrote:swinging the leg

Typo?



It means ‘being lazy’ - his numbers seem well down!

Is it a Scottish saying maybe?


It's originally a sailing term with lead rather than leg, as dath's said. Something to do with counting knots iirc.
User avatar
Tokyo Sexwale
 
Posts: 48836
Joined: Thu Jan 24, 2008 5:14 pm

Re: vlookupwtf

Post by Dirk @ Mon May 07, 2018 9:30 am

morebeanz wrote:The list you are looking up from needs to be in sequential order, if I recall correctly - in this case alphabetic...


Spent from masturbating with my iprick using Tapatalk

believe this is the correct explanation, however
In Excel, they don't need to be, providing that the 4th parameter is set to false. In this case it is not included, so if it works the same then it defaults to true.
User avatar
Dirk
 
Posts: 30170
Joined: Mon Oct 22, 2007 8:03 pm
Highscores: 5

Re: vlookupwtf

Post by Disastrous @ Mon May 07, 2018 9:41 am

Interesting! I had no idea - I’d picked it up from someone else using it and had assumed it to mean being laid back with one’s leg jauntily swinging over the other rather than working.

The actual meaning makes perfect sense though.
User avatar
Disastrous
Monkey Licker!
 
Posts: 39121
Joined: Thu Feb 28, 2008 3:58 pm

Re: vlookupwtf

Post by deev @ Tue May 08, 2018 3:49 pm

morebeanz wrote:The list you are looking up from needs to be in sequential order, if I recall correctly - in this case alphabetic...


Spent from masturbating with my iprick using Tapatalk

Boom! Thanks!
User avatar
deev
Banger King
 
Posts: 14679
Joined: Mon Oct 22, 2007 8:14 pm
Location: Giffnock
Highscores: 3

Re: vlookupwtf

Post by deev @ Tue May 08, 2018 3:52 pm

Disastrous wrote:No idea, but Robert seems to be swinging the leg a bit!

He is indeed! Thankfully they're all just made up people, excel isn't something I'm required to use on my job but I've started a wee pet side project for recording things and displaying data more easily for presentations and whilst I can do basics I'm trying to teach myself more complicated stuff.
User avatar
deev
Banger King
 
Posts: 14679
Joined: Mon Oct 22, 2007 8:14 pm
Location: Giffnock
Highscores: 3

Re: vlookupwtf

Post by ttwiggy @ Tue May 08, 2018 4:19 pm

Tokyo Sexwale wrote:
Disastrous wrote:
Tokyo Sexwale wrote:
Disastrous wrote:swinging the leg

Typo?



It means ‘being lazy’ - his numbers seem well down!

Is it a Scottish saying maybe?


It's originally a sailing term with lead rather than leg, as dath's said. Something to do with counting knots iirc.


Depth (of the water under the boat) rather than speed (knots), but yes, it's a nautical thing. As is toeing the line.
ttwiggy
 
Posts: 1732
Joined: Tue Aug 11, 2015 3:54 pm

Re: vlookupwtf

Post by Käsemeister @ Tue May 08, 2018 4:46 pm

Except they did literally count knots. In the rope.
User avatar
Käsemeister
 
Posts: 36963
Joined: Thu Feb 28, 2008 5:24 pm
Location: Hanging on in quiet desperation.

Re: vlookupwtf

Post by ttwiggy @ Tue May 08, 2018 4:50 pm

Actually, it's 'heaving' the lead. And it was quite a strenuous task. So it appears that Dis may be correct, as 'swinging the leg' was apparently an old army term for shirking responsibility and may have evolved into 'swinging the lead'.
ttwiggy
 
Posts: 1732
Joined: Tue Aug 11, 2015 3:54 pm

Re: vlookupwtf

Post by Pigeon @ Tue May 08, 2018 4:55 pm

Speed measurement: chuck a line over with a drogue on the end and count how many regularly-spaced identical knots ran out in a given time.

Depth measurement: chuck a line over with a lead weight on the end and see which different knot or thing tied into the line the water comes up to.
User avatar
Pigeon
CTF Technical Expert
CTF Technical Expert
 
Posts: 23917
Joined: Thu Feb 28, 2008 8:50 pm
Location: All alone in the crazy city

Re: vlookupwtf

Post by Dirk @ Tue May 08, 2018 4:59 pm

ttwiggy wrote:Actually, it's 'heaving' the lead. And it was quite a strenuous task. So it appears that Dis may be correct, as 'swinging the leg' was apparently an old army term for shirking responsibility and may have evolved into 'swinging the lead'.

Heaving the lead was when they were measuring depth properly

It has been suggested that swinging the lead was the term used when sailors, rather than dropping the weight and then pulling it up again, just swung it, and called out fake depths, hence why it became associated with lazy.

However I believe there is some doubt as to the validity of this etymology

[/gma]
User avatar
Dirk
 
Posts: 30170
Joined: Mon Oct 22, 2007 8:03 pm
Highscores: 5

Re: vlookupwtf

Post by ttwiggy @ Tue May 08, 2018 5:02 pm

Dirk wrote:
ttwiggy wrote:Actually, it's 'heaving' the lead. And it was quite a strenuous task. So it appears that Dis may be correct, as 'swinging the leg' was apparently an old army term for shirking responsibility and may have evolved into 'swinging the lead'.

Heaving the lead was when they were measuring depth properly

It has been suggested that swinging the lead was the term used when sailors, rather than dropping the weight and then pulling it up again, just swung it, and called out fake depths, hence why it became associated with lazy.

However I believe there is some doubt as to the validity of this etymology

[/gma]


It would be hard to 'pretend' as the weight also collected silt and other stuff from the sea floor/river bed that the ship's master/sailing master would want to see.
ttwiggy
 
Posts: 1732
Joined: Tue Aug 11, 2015 3:54 pm

Re: vlookupwtf

Post by Dirk @ Tue May 08, 2018 5:11 pm

ttwiggy wrote:
Dirk wrote:
ttwiggy wrote:Actually, it's 'heaving' the lead. And it was quite a strenuous task. So it appears that Dis may be correct, as 'swinging the leg' was apparently an old army term for shirking responsibility and may have evolved into 'swinging the lead'.

Heaving the lead was when they were measuring depth properly

It has been suggested that swinging the lead was the term used when sailors, rather than dropping the weight and then pulling it up again, just swung it, and called out fake depths, hence why it became associated with lazy.

However I believe there is some doubt as to the validity of this etymology

[/gma]


It would be hard to 'pretend' as the weight also collected silt and other stuff from the sea floor/river bed that the ship's master/sailing master would want to see.

And the sailor involved would likely be monitored fairly closely. That is part of the reason for the doubt cast. Another is that the first recorded use was much later than would otherwise expect, and in an army context, not naval.

More interestingly is how much of the coast of the world (and the UK) is still charted using measurements from those days of lead and rope from sailing and rowing boats. Until recently only the main routes and channels have been re-surveyed with more modern equipment- though a lot more is now being done. The vast majority of it (as in the world) was done by the RN
User avatar
Dirk
 
Posts: 30170
Joined: Mon Oct 22, 2007 8:03 pm
Highscores: 5

Re: vlookupwtf

Post by Disastrous @ Tue May 08, 2018 5:23 pm

So I was right all along then?

Thought so.
User avatar
Disastrous
Monkey Licker!
 
Posts: 39121
Joined: Thu Feb 28, 2008 3:58 pm


Return to Computers & Gaming & Gadgets

Who is online

Users browsing this forum: No registered users and 1 guest

yt
  Enable youtube titles