Jump to content

Excel Help


Recommended Posts

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

Link to post
Share on other sites

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/XjzG1jXIHApK42fbFxay.png

Link to post
Share on other sites

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

Link to post
Share on other sites
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.
Link to post
Share on other sites

I try to avoid ever using absolute vlookup references for that reason. the workaround, if you have to use absolute, is to insert cells in the range when you want to add more vlookup values. excel will adjust the formulas where you're doing the vlookup to include the new inserted cells. but it's a total pain compared to just click-shift-click on column headers. you probably knew this though.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...