 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2004need help with lookup
Let me try and explain what Im trying to do. I have a spreadsheet for ordering parts for photocopiers. Then I have individual worksheets with the parts for each model copier. What I want to be able to do is on the main sheet, set it up in the following way. Cell 1 is the part number Cell 2 is the part description. cell 3 and 4 are for quantity and one other figure. I want to make cell 5 a drop list to choose what model copier i want the part for, and when that is chosen, cell 2 becomes a dropdown list, listing all the parts on that model copier from the appropriate worksheet. Then when I select the part I want, the part number is added to cell 1 automatically. I can make this spreadsheet work if I just had one model copier on another worksheet, but how can I use multiple worksheets for different models? TIA Geoff... Hi try the following: 1. Name your spreadsheets with the model details like the model name you would choose in your listbox in cell 5 (e.g. model_1,model_2,etc.) 2. On each model detail sheet do the following: - select the range with the part names (e.g. this is column B on each sheet) - goto 'Insert - Name - Define' and choose a name which is composed of the model name and '_list'. e.g. model_1_list, model_2_list, etc. 3. select cell 2 (assumption: B1) and goto 'Data - Validation - List' and enter the following formula =INDIRECT(E1&"_list") assumption: cell E1 is your cell 5 with the modell drop down list 4. in cell A1 (your cell 1) enter the following formula (assumption: col. a on your model detail sheets stores the part number and col. B the part description) =IF(B1<>"",INDEX(INDIRECT("'" & E1 & "'!A1:A100"),MATCH(B1,INDIRECT("'" & E1 & "'!B1:B100"),0)),"")  Signature-- Regards Frank Kabel Frankfurt, Germany > Let me try and explain what Im trying to do. > [quoted text clipped - 19 lines] > TIA > Geoff... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.