Drop down box values

J

Jennifer

I've been given this task to do in Excel and I need help.

I have a column/list in worksheet2. The list is indexed and must stay intact
as many cells in the workbook use this list.

Lets say in this column I have the following information:
Absorp
ABSC0112
ABSC0129
ABSC0148
Cen
CVAE 18
CVAE 19
CVAE 21
Wate
GEVA 1/2 - 2 1/2
GEVA 3 -5
GEHA 1/2 - 2 1/2
GEHA 3 -5

Now I want to add two drop down boxes on another worksheet. Drop down box 1
would have the values:
Absorp
Cen
Wate

The list in Drop down box 2 would be dependent on what value is selected in
drop down box 1 so if Absorp is selected in drop down box1 then ABSC0112,
ABSC0129 and ABSC0148 would display in drop down box 2.

Any ideas on how to do this?
Thanks in advance.
 
A

Arvi Laanemets

Hi

Redesign your list on worksheet2
Absorp ABSC0112 CVAE 18 GEVA 1/2 - 2 1/2
Cen ABSC0129 CVAE 19 GEVA 3 -5
Wate ABSC0148 CVAE 21 GEHA 1/2 - 2 1/2

Define named ranges (Insert.Name.Define)
List1=Worksheet2!$A$1:$A$3
List2a=Worksheet2!$B$1:$B$3
List2b=Worksheet2!$C$1:$C$3
List2c=Worksheet2!$D$1:$D$3

On another worsheet, select a cell where you want 1st dropdown (A1 in my
example), and then from menu Data.Validation.List, and into source field
enter the formula:
=List1

Select a cell where you want 2nd dropdown, and again create a validation
list - with source:
=IF(A1="Absorp",List2a,IF(A1="Cen",List2b,List2c))
 
Top