2nd list of values depends on selection in 1st list of values 2

[electricpete]

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?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

btw - all data choices are text

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[WMO]

electricpete,
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

 

[electricpete]

That is EXACTLY what I was looking for.

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.

 

[bltseattle]

You need to name all your lists.

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:

Choice1List    Data1        Data2
Data1          Data3        Data6
Data2          Data4        Data7
               Data5        Data8

For the first drop box, say in cell $A$1, use the data validate>list>
=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):

Choice1List    List1        List2        Lookup1
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))

 

[WMO]

Yes and that eliminates the need to redefine your ranges if you have to add additional associated values to an option. I am glad that it was useful.