Excel 2003 Drop Down Menu

  • Thread starter Thread starter Pheng
  • Start date Start date
P

Pheng

Hi all,

I have one file called Invoice Template.

In it I have 1 worksheet with the Template. As all invoices, I have
three colums that includes Part#, Model and Description.

Therefore I have a list of items with the following headings: Part#,
Model and Description on a worksheet called DataSheet.

I would like it so that in the invoice worksheet, I can select the
Model from a drop down menu and to the left, the part# and to the
right, the description both automatically pops up according to the
selected model.

I am not very advanced with Excel, so please assist by speaking plain
english and explain the formula please.
 
Hi
try the following
1. Create the dropdown:
-> Define a name for your model names (goto 'Insert - Name - Define
Name'). Call this name for example 'Prod_Models'
-> Enter the following formula for this name:
='DataSheet'!$B$2:$B$999
-> this name references your product model names
-> on your main sheet select the cells in column B there you want
your drop down listbox
-> goto 'Data - Validation'. Choose 'List'
-> Enter the formula: =Prod_Models

2. Automatic fill of col A + B (note: this works only if your model
name is unique in your list!)
-> In A1 (assumption: B1 stores the listbox and your product model)
enter the formula

=IF(B1<>"",INDEX(''DataSheet'!$A$2:$A$999,MATCH(B1,'DataSheet'!$B$2:$B$
999,0),0),"")
-> copy down for all desired rows
-> in C1 enter the following formula:

=IF(B1<>"",INDEX(''DataSheet'!$C$2:$C$999,MATCH(B1,'DataSheet'!$B$2:$B$
999,0),0),"")
-> copy down for col. C
 
Back
Top