20 replies to this topic

[dna4ever]

I couldn't find the existing Excel Help topic so created this one.I think I need a v lookup or if/then type statement I believe? I have one spreadsheet that is constant and has a CustomerID and AccountRep. I have a second spreadsheet that is variable and updated weekly with customer revenue by that same CustomerID in the first spreadsheet. So the 2nd sheet has CustomerID and Revenue and I want to add a 3rd column that automatically reads the first spreadsheet and when it finds the CustomerID it matches and posts the corresponding AccountRep into the 3rd column of the 2nd sheet. Hope that makes sense. I screen capped a fake example to help illustrate: http://vvcap.net/db/...HMt5E6x1rvK.pngThere are about 1,000 customer IDs and only 4 reps if that matters

[DJ Vu]

Yeah, vlookup would work.Using your examples (and assuming the first spreadsheet was called "database"), it would look like this:=VLOOKUP(A2,[database.xls]Sheet1!$A$2:$B$5,2,FALSE)

[Napa Lite]

Man, the one time I can be of use to someone and DJ scoops me.

[dna4ever]

I'm doing something wrong, I have these in Sheet 1 and Sheet 2 now:Sheet 1 is the master file:http://vvcap.net/db/...CSBOdN2ut47.pngSheet 2 is where I'm putting the VLOOKUP to fill in the AccountRephttp://vvcap.net/db/...HDBJnxtgNUg.pngIt appears to just be putting the accountrep in the same order as on sheet 1 versus actually looking for the customerid and matching the correct accountrep?EDITOooops, here is the code I'm using as wellhttp://vvcap.net/db/...ApK42fbFxay.png

[DJ Vu]

dna4ever, on 30 July 2012 - 12:53 PM, said:

EDITOooops, here is the code I'm using as wellhttp://vvcap.net/db/...ApK42fbFxay.png

You're missing the first cell reference that you're looking up.You have: =vlookup(Sheet1...Should be: =vlookup(A2,Sheet1...Edit: Oh, I see, you're not missing the reference, you just have two Sheet1's. My fix still works though.

[dna4ever]

what the hell, says too many arguments for function if I add A2, in the beginning.The master is on Sheet 1, the variable is on Sheet 2pisses me off when something is so simple and I still struggle with it:http://vvcap.net/db/...bPLNUWuXjnD.png

[DJ Vu]

dna4ever, on 30 July 2012 - 01:05 PM, said:

what the hell, says too many arguments for function if I add A2, in the beginning.The master is on Sheet 1, the variable is on Sheet 2pisses me off when something is so simple and I still struggle with it:http://vvcap.net/db/...bPLNUWuXjnD.png

Get rid of the first "Sheet1!A2."

[dna4ever]

ok closer, it worked for 3 of the 4. Any idea why ABC would say N/A ?http://vvcap.net/db/...Cx88YoIOmJP.png

[dna4ever]

Should I sort the master Sheet 1 list Alphabetically or something? Been reading some stuff that says to do that?actually, in this example the customerid is alphabetical on sheet 1, just not sheet 2

[dna4ever]

=VLOOKUP(A4,Sheet1!$A$2:$B$5,2)appeared to work

[DJ Vu]

dna4ever, on 30 July 2012 - 01:12 PM, said:

ok closer, it worked for 3 of the 4. Any idea why ABC would say N/A ?http://vvcap.net/db/...Cx88YoIOmJP.png

Yes. When you entered the part where it says "A2:B5" and copied the formula down, it moved that part down too.Change the first formula to $A$2:$B$5 and then copy it down.Edit: Well then. And it doesn't matter if you sort alphabetically.

[dna4ever]

thanks for the help, working like a charm

[DJ Vu]

dna4ever, on 30 July 2012 - 01:27 PM, said:

thanks for the help, working like a charm

Some day, and that day may never come, I will call upon you to do a service for me.

[FCP Bob]

DJ Vu, on 30 July 2012 - 01:30 PM, said:

Some day, and that day may never come, I will call upon you to do a service for me.

May you have a manly child.

[JubilantLankyLad]

masculine

[FCP Bob]

I knew I should have googled it first

[ShakeZuma]

this was a thoroughly enjoyable read, all around

[InternetExplorer]

the reference to sheet 1 can be A:B if no other data is below it. saves some $.I had a problem in here but the formatting went idiotic when I hit submit and I can't be arsed to make images for it in imgur.

[Ron_Mexico]

Jesus, I need to take a class or something.

[BaseJester]

InternetExplorer, on 30 July 2012 - 06:03 PM, said:

the reference to sheet 1 can be A:B if no other data is below it. saves some $.

Good suggestion.It also saves a lot of head-scratching when you add values to the bottom of the list and some of the lookups don't work like you expect.