Conditional Data Validation in Google Sheets, limit second drop-down based on first drop-down

0
votes

I want to select a category in a drop down, then limit the next drop-down in that row to items only in that category, then lookup the value for that item and display it from a data table. I would prefer to avoid scripting if at all possible. I swear I've done it before, but maybe it was in Excel...

I have a table where first column represents category and second represents item. Categories are non-unique, Items are unique. I want a "selection" sheet where the first column chooses from available Categories in the first column of data and second column chooses from Items that have that category in column 1 in the data table. Pictures to show desired end result, but set manually. How do I make this happen dynamically? In this case, I cannot have a header of "A, B, C" with the items listed below, as each item has it's own value that will be pulled with a lookup in the third column of the selection area.

In selection area, first column has "A" selected, so options are only "Z", "X", and "Y" for second drop down.

First column has "A" selected, so options are only "Z", "X", and "Y" for second drop down.

In selection area, first entry now has "B" selected, so options are only "W" and "V" for second drop down.

First entry now has "B" selected, so options are only "W" and "V" for second drop down.

1
validationgoogle-sheets

1 Answers

0
votes

You can do it with a combination of named ranges and =INDIRECT()

Workflow:

  • Go on Data -> Named ranges and create named ranges in column B corresponding to each different value of column A

enter image description here

enter image description here

  • In column E, create a data validation with column A as range
  • Select a dummy column (e.g. D) and paste the formula =INDIRECT(E1) - this will display in this column the data belonging to the named range chosen in column E
  • Create a second validation in column F taking column D as range enter image description here
  • This will result in a dependent dropdown - you can set the auxilliary column D to hidden.

enter image description here