2nd list of values depends on selection in 1st list of values
2nd list of values depends on selection in 1st list of values
(OP)
In previous thread someone told me a great way to provide a list of values:
Use data / validation / list and enter “=listname” where listname is a named range containing the candidate values.
Now I have a new question. I would like to choose from a list of values for 1, and then have the choices for values for column 2 dependent upon my selection already made in column 1.
For example,
if I choose data1 in column 1, my choices for column 2 would be data3,data4, data5.
if I choose data2 in column 1, my choices for column 2 would be data6,data7, data8
I could in theory accomplish the same thing by merging columns 1 and 2 and providing choices:
data1 data3
data1 data4
data1 data5
data2 data6
data2 data7
data2 data8
However I don’t like the fact that I will have an overwhelming number of choices. Would like to keep my list of values smaller so it is easier to choose from.
So it there anyway to provide the list of values data3,data4,data5 for column2 if column1 contains data1... and a different list of values data6,data7,data8 for column2 if column1 contains data2?
Use data / validation / list and enter “=listname” where listname is a named range containing the candidate values.
Now I have a new question. I would like to choose from a list of values for 1, and then have the choices for values for column 2 dependent upon my selection already made in column 1.
For example,
if I choose data1 in column 1, my choices for column 2 would be data3,data4, data5.
if I choose data2 in column 1, my choices for column 2 would be data6,data7, data8
I could in theory accomplish the same thing by merging columns 1 and 2 and providing choices:
data1 data3
data1 data4
data1 data5
data2 data6
data2 data7
data2 data8
However I don’t like the fact that I will have an overwhelming number of choices. Would like to keep my list of values smaller so it is easier to choose from.
So it there anyway to provide the list of values data3,data4,data5 for column2 if column1 contains data1... and a different list of values data6,data7,data8 for column2 if column1 contains data2?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.





RE: 2nd list of values depends on selection in 1st list of values
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: 2nd list of values depends on selection in 1st list of values
This will work.
The easiest way to describe it is on a blank workbook so you can follow along.
In a blank workbook, on SHEET2 enter the following:
A1 contains DATA1
A2 contains DATA2
1. Select cells A1 and A2
2. On the menu bar, INSERT...NAME...DEFINE
Name it OPTIONS
Enter the following
B1 contains DATA3
C1 contains DATA4
D1 contains DATA5
B2 contains DATA6
C2 contains DATA7
D2 contains DATA8
SELECT CELLS B1, C1, D1 and name this range DATA1
SELECT CELLS B2, C2, D2 and name this range DATA2
GO TO SHEET1
SELECT COLUMN 'A' set up a data validation rule
LIST....and the source will be =OPTIONS
SELECT column 'B' set up a data validation rule
LIST...and the source will be =INDIRECT(A1)
regards,
Mark
RE: 2nd list of values depends on selection in 1st list of values
For housekeeping it will be easy to set the options accross the top row of sheet1 and all the associated values in a the column below each. Then I can assign all values with Insert/Name/Create/Names-In-Top-Row
Thx again!
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: 2nd list of values depends on selection in 1st list of values
You then use the INDIRECT function. It works like this. By your example, here are the lists - the first row is the name of the list, following rows are the items:
CODE
Data1 Data3 Data6
Data2 Data4 Data7
Data5 Data8
=choice1list
For the second list box, use the data validate>list>
=INDIRECT($A$1)
This will work if the choice and the name of the list are the same.
If they are not, you need a lookup table, like this (again, names are in the row above):
CODE
Data1 Data3 Data6 Data1 List1
Data2 Data4 Data7 Data2 List2
Data5 Data8
Now, the second drop box formula is:
=INDIRECT(VLOOKUP($A$1,Lookup1,2))
RE: 2nd list of values depends on selection in 1st list of values