Data Validation & Drop-Down Lists

F

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.
 
F

fly61265

Hi Luke,

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top