Drop down box values

  • Thread starter Thread starter Jennifer
  • Start date Start date
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.
 
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))
 
I'm sorry but I don't see how this would work with drop down boxes I've
created using the forms menu.
Should/could I use a macro to do this? Something like if drop down box 1 has
value Absorp then drop down box 2 should display ABSC0112, ABSC0129,
ABSC0148 (or preferably a dynamic list reference).

Thank you,
Jennifer
 
Back
Top