Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula on link and drop down list details
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula on link and drop down list details
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 Kelly Lim wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I link a drop down list to other worksheets | Excel Worksheet Functions | |||
how do I link a drop down list entry to a new drop down cell? | Excel Discussion (Misc queries) | |||
multiple select from the drop down list in excel. list in one sheet and drop down in | Excel Discussion (Misc queries) | |||
link email to drop down list | Excel Discussion (Misc queries) | |||
link to result from drop down list | Links and Linking in Excel |