How do I write a formula to obtain information from another spread

[sue_b2000]

I have a spreadsheet that contains 6 different worksheets. In the first
worksheet, in the first column, I have the client's last name. In another
column I have the sales representative's name.

In the subsequent worksheets, I would like to link the same salesperson to
the client. Not all of the worksheets contain the same information and all
the worksheets contains lists that I use to sort by.

When I wrote a function, it referenced the cell name rather than the clients
name (contents of the cell). Is there a way to reference the clients name,
link it to the sales person in the subsequent worksheets?

 

[Max]

Is there a way to reference the clients name,

link it to the sales person in the subsequent worksheets?

If its a unique client - sales rep mapping (1 to 1), then no problem.
A standard index n match should work fine

Assume in Sheet1,
in A2:B2 down you have the client - sales rep mapping

Then in another sheet,
Assume sales reps are listed in A2 down
Put this in B2: =INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0))
Copy B2 down to return the clients mapped to the sales reps

Perhaps better with an error trap to return neater looking blanks for any
unmatched cases, you could use in B2, copied down:
=IF(ISNA(MATCH(A2,Sheet1!B:B,0)),"",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---