lookup the next matching record.

[tess]

I am trying to use a spreadsheet that was downloaded from our manufacturing
software. Now I need to sort the data by our sales rep and create a
worksheet for each rep. My problem is when I do a VLOOKUP to find a sales
rep it gives me the first account number over and over as I copy the formula
down. How do I get it to give me the next account information?

(The seemingly easy solution is to sort by Sales Rep and copy/paste the info
into a new worksheet but the is just an example, our reps and account are
very extensive and that would take a long time and I am going to have to do
this on a regular basis.)

Sales Rep Acct # Accssry1 Accssry2 Accssry3
Bob 12345 568 625 682
Sue 12413 700 757 814
Bob 12481 832 889 946
Sue 12549 964 1021 1078
Bob 12617 1096 1153 1210
Sue 12685 1228 1285 1342

 

[Gord Dibben]

tess

Sounds like you have calculation mode set to manual.

Tools>Options>Calculation. Set to Automatic.

Or your formula is using Absolute Reference like $A$1 in the lookup value.

Change to Relative.

=VLOOKUP(A1,table_range,column,FALSE)

Note: if using cell refs for the table_range, those should be Absolute as in

$B$1:$E$100


Gord Dibben MS Excel MVP

 

[PBalmanno]

I am trying to use a spreadsheet that was downloaded from our manufacturing
software. Now I need to sort the data by our sales rep and create a
worksheet for each rep. My problem is when I do a VLOOKUP to find a sales
rep it gives me the first account number over and over as I copy the
formula
down. How do I get it to give me the next account information?

(The seemingly easy solution is to sort by Sales Rep and copy/paste the
info
into a new worksheet but the is just an example, our reps and account are
very extensive and that would take a long time and I am going to have to
do
this on a regular basis.)

Sales Rep Acct # Accssry1 Accssry2 Accssry3
Bob 12345 568 625 682
Sue 12413 700 757 814
Bob 12481 832 889 946
Sue 12549 964 1021 1078
Bob 12617 1096 1153 1210
Sue 12685 1228 1285 1342

I had a similar situation which was answered here, I inserted a row in
between:

Sales Rep Acct # Accssry1 Accssry2 Accssry3
(insert row)
Bob 12345 568 625 682

then choosing the empty cell under the last column in that blank row I chose
from the menu above:
Data / Filter / Autofilter
Drop down boxes will be inserted in each blank cell in the empty row for
each column that will allow you to choose based on any item for example you
wan a list of all Bob's or a combination of Bob's and Account # ????? etc.

 

[Max]

One formulas option to auto-copy lines by sales rep to their own separate
sheets ..

Try this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "State" col, which may
contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key col will
be auto-copied to the sheet named: NY, and appear neatly bunched at the top.
Ditto for lines with "CA", "NV", etc which will be copied into their
respective sheets.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the next key col value. Eg we
first formulate one child sheet for "NY", dress it up nicely, then just make
copies of the "NY" sheet, and rename these as: CA, NV, SD, etc.

In your case, the key col would be the sales rep (col A), eg: Bob, Sue, etc

 

[tess]

Thanks everyone. Max, that is exactly what I needed, I would have never
figured it out on my own!

Tess

 

[Max]

Great to hear that. You're welcome, Tess.