Excel - data validation based off filtered column

0
votes

I have two sheets, one setup as a data (available values), and another working sheet.

Working Sheet:

Working Sheet

Data Sheet

enter image description here

So using data-validation, I pull in all columns A1:Z1 in data sheet into a drop down list in working sheet B3. Depending on the chosen area, I then want to populate the sub area drop down with the associated column rows.

So in this case, 'Fruit' is chosen, therefore drop down should display values from 'Data!A3:A'

Any ideas on automating this?

1
excelspreadsheetvlookup

1 Answers

1
votes

First, create Named Ranges for each of your categories:

enter image description here

Then, for the Data Validation that changes depending on what category you choose, you will create a new List Data Validation, using a formula:

=INDIRECT($B$3)

enter image description here

Then, as you change B3, the list will update. Note Without VBA (as far as I know you can't do with a formula), changing B3 will not update the choice in C3. You still have to manually change that, but the list will indeed update.

enter image description here

Edit: If you have 26 columns with data, all different sizes, you can use VBA to create named ranges for those.