Auto filling information from Data List
I have a Data List A1:E10 with the first column listing by "Unit number" (501
- 509). In the remaining 4 colums I have 4 different vehicle types each of which have a list of corresponding vehicle numbers for a given unit number. My requirement is to use this table array as a reference and somewhere else in the workbook I have a title area where I want to select a Unit Number from the list (I can select this from a validation list) which automatically inserts the corresponding vehicle numbers against the 4 vehicle types. I cannot find which is the correct approach to solve this problem although I have just been and bought Excel 2003 for Dummies, I still cannot decide the correct and appropriate method, any help would be appreciated. |
Auto filling information from Data List
Say you enter the unit number in A1.
Maybe using data|validation so that you can't make a typo!!! Visit Debra Dalgleish's site for lots of info: http://www.contextures.com/xlDataVal01.html Then you can retrieve the first value (column B of the table) with something like: =if($a$1="","",vlookup($a$1,sheet2!a:e,2,false) the 3rd column: =if($a$1="","",vlookup($a$1,sheet2!a:e,3,false) .... The 5th column: =if($a$1="","",vlookup($a$1,sheet2!a:e,5,false) It doesn't do any error checking, since you used data|validation in A1 and you can't make a typo!!! Debra Dalgleish has lots of notes on =vlookup() he http://www.contextures.com/xlFunctions02.html and http://contextures.com/xlFunctions02.html#Trouble Paul wrote: I have a Data List A1:E10 with the first column listing by "Unit number" (501 - 509). In the remaining 4 colums I have 4 different vehicle types each of which have a list of corresponding vehicle numbers for a given unit number. My requirement is to use this table array as a reference and somewhere else in the workbook I have a title area where I want to select a Unit Number from the list (I can select this from a validation list) which automatically inserts the corresponding vehicle numbers against the 4 vehicle types. I cannot find which is the correct approach to solve this problem although I have just been and bought Excel 2003 for Dummies, I still cannot decide the correct and appropriate method, any help would be appreciated. -- Dave Peterson |
Auto filling information from Data List
Many thanks Dave, I shall investigate further and let you know when I find
the answer I am looking for. Regards, Paul. "Dave Peterson" wrote: Say you enter the unit number in A1. Maybe using data|validation so that you can't make a typo!!! Visit Debra Dalgleish's site for lots of info: http://www.contextures.com/xlDataVal01.html Then you can retrieve the first value (column B of the table) with something like: =if($a$1="","",vlookup($a$1,sheet2!a:e,2,false) the 3rd column: =if($a$1="","",vlookup($a$1,sheet2!a:e,3,false) .... The 5th column: =if($a$1="","",vlookup($a$1,sheet2!a:e,5,false) It doesn't do any error checking, since you used data|validation in A1 and you can't make a typo!!! Debra Dalgleish has lots of notes on =vlookup() he http://www.contextures.com/xlFunctions02.html and http://contextures.com/xlFunctions02.html#Trouble Paul wrote: I have a Data List A1:E10 with the first column listing by "Unit number" (501 - 509). In the remaining 4 colums I have 4 different vehicle types each of which have a list of corresponding vehicle numbers for a given unit number. My requirement is to use this table array as a reference and somewhere else in the workbook I have a title area where I want to select a Unit Number from the list (I can select this from a validation list) which automatically inserts the corresponding vehicle numbers against the 4 vehicle types. I cannot find which is the correct approach to solve this problem although I have just been and bought Excel 2003 for Dummies, I still cannot decide the correct and appropriate method, any help would be appreciated. -- Dave Peterson |
All times are GMT +1. The time now is 10:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com