need help with lookup

[Calsonic]

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...

 

[Frank Kabel]

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)),"")