Data Validation & Drop-Down Lists

[fly61265]

I am setting up drop-down lists in a simple spreadsheet.

Column 1 - Manufacturer
Column 2 - Series Code
Column 3 - Cavity Group

I have lists set up in a list tab. Depending on which manufacturer is
selected a specific Series Code drop-down occurs.

I have seen a lot of help reference about setting up a third dependent list.
I don't need this.

I want Cavity Group to supply a drop-down that also references the
"manufacturer" selected...it doesn't matter which series code is selected.

 

[Luke M]

You should be able to set it up as a dynamic list tied to the initial choice.
See link for ideas:
http://www.contextures.com/xlDataVal02.html

For your case, simply make two validations based off of one criteria.

 

[fly61265]

Hi Luke,

Could you please help me with "simply make two validations based off of one
criteria"?

 

[Luke M]

Walkthrough for 2 item lists:

Let's say your first dropdown's list is in A2:A3.
Drop down #2's list then takes columns B & C
Drop down #3's list takes column D & E

Your actual drop downs are in J1, K1, L1

Insert-Name-Define
Name: Dropdown2
Formula is:
=OFFSET($B$2,0,-1+MATCH($J$1,$A$2:$A$3,0),2,1)
Click "add"

Name: Dropdown3
Formula is:
=OFFSET($D$2,0,-1+MATCH($J$1,$A$2:$A$3,0),2,1)
Click "add", then "ok"

Select K1
Data-Validation-List
=Dropdown2

Select L1
Data-Validation-List
=Dropdown3

Note how both named ranges are based off of J1 (your first criteria), I've
simply changed the anchor point for each offset. To increase the size of your
dependent lists, change the 2 near the end to number of rows you need. If you
need more rows in independent list, add columns and change references
accordingly.

 

[fly61265]

PERFECT! Thanks for your help!