formula on link and drop down list details

[Kelly Lim]

Hi everyone,

Let's say i have a spreadsheet with a column of:
NAME SURNAME PASSPORT NO.
kelly
cheryl

i then have another spreadsheet containing the details:

SURNAME PASSPORT NO.
lim 123456
liew 789123

i then create a drop down list from another spreadsheet consisting of
"kelly" & "cheryl", what ways can i do or link?? when i select on "kelly" and
on the first spreadsheet, the next column would appear the details itself-
lim (surname) 123456 (passport no.)

Hope to hear from you soon.
Thank you

Warmest Regards,
Kelly

 

[Dave Peterson]

I think that you'd need a unique identifier in both sheets. And first names
usually aren't enough. (Last names may not be enough either--your list will
have to have no duplicates in that unique identifier field.)

But say you used the Lastname as the id.

Then you could use a formula like this to retrieve the values:

=if(a2="","",vlookup(a2,sheet2!a:c,2,false)
and
=if(a2="","",vlookup(a2,sheet2!a:c,3,false)

Excel will bring back the corresponding value in column B for the first match it
finds--that's why it has to be unique.

And you could use Data|Validation to choose that unique identifier.

See Debra Dalgleish's site for some nice notes:
http://contextures.com/xlDataVal01.html
and
http://contextures.com/xlFunctions02.html