Dave,
Thanks for your recommendation -- I was looking into your suggestion, then
found that my code -- (=VLOOKUP(F2,Salesperson!$A$2:$B$1500,2,TRUE) --
entered in Cell K2 with a slight modification of the code -- gave me exactly
what I was looking for -- I just needed to change to Absolute Reference and
change FALSE to TRUE:)
-- "Dave Peterson" wrote in message
...
I would spend some time putting a key name on each of the rows in that
second
worksheet.
Doctorjones_md wrote:
Dave,
Thank you for the quick reply -- I want to return the value from column B
of
Salesperson2, so I had the following formula in Cell K2 of Salesperson1:
=VLOOKUP(F2,Salesrep1!A2:B1500,2,FALSE)
This would work great if the data in each worksheet was evenly matched.
Here's my problem ...
In Salesperson1, I have 1500 entries (a total of 102 sales reps spread
over
1500 transactions). The first 20 rows (A2:A21) in Salesperson1 are for
sales rep Scott Anderson, but the only reference to Scott Anderson is in
cell A3 of Salesperson2. I need for the code to take the value of
Salesperson1!Ax (where x is the row) and find that value in
Salesperson2!Ax
(where x is the row) and return the value of Salesperson!Bx (where x is
the
row) in Salesperson1!Kx (where x is the row).
Example of data:
Worksheet SALESPERSON1
Worksheet SALESPERSON2
Salesperson (Cell Ax) Department (cell Kx)
Salesperson (Cell Ax) Department (Cell Bx)
Scott Anderson
Greg Albert 10
Scott Anderson
Scott Anderson 5
Scott Anderson
Tina Alyson 2
Scott Anderson
Debbie Baker 1
Scott Anderson
Tim Davis 7
Scott Anderson
Scott Anderson
Scott Anderson
Tim Davis
Any ideas on how to achieve this?
Thanks again for your help.
, and the
"Dave Peterson" wrote in message
...
I may be missing something, but it looks like you want to match up the
value in
F2 to something in column A of the worksheet name salesperson2 and
return
the
value from column F.
If that's the case, then this would go in K2:
=vlookup(f2,SalesPerson2!$a$2:$F$4000,6,false)
Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html
Doctorjones_md wrote:
What would be the best way to approach this?
I have a hugh list of transactions that I need to sort on -- each
transaction has a Salesperson with falls into (2) distinct Sales
Categories:
(Private) & (Commercial).
For reasons beyond my control, the decision was made not to include
these
category fields on the worksheets, so I created a listing of all the
Salesperson with their corresponding Sales Categories in a separate
worksheet.
I have the following code -- which does a VLOOKUP on Cell F2
(Salesperson1!F2) against Salesperson2!A2:A4000)
CODE:
=VLOOKUP(F2,Salesperson2!A2:A4000,1,FALSE)
Here's what I'm trying to do ...
I'd like use the values in Salesperson!F2:F4000 and search for these
valuse
in Salesperson!A2:A4000 -- for each corresponding MATCH (ie,
Salesperson1!F2 = Salesperson2!A2, insert the value of Salesperson2!B2
into
Salesperson1!K2).
What would be the best way to approach this? I suspect that I'll need
to
Loop through records Salesperson1!F2:F4000.
Thanks in advance
--
Dave Peterson
--
Dave Peterson