×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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?

=====================================
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

(OP)
btw - all data choices are text

=====================================
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

2
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





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

(OP)
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.

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

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:

CODE

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):

CODE

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))




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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close