Data Validation - Dynamic Dropdowns

**Pourradass** · 08-12-2014, 10:18 AM

Hi everyone,

I am trying to build a spreadsheet containing data validation (drop-downs). So far, so good.

My goal:
* I want to have various cells show dropdowns based on the value of a cell (per row) which itself can be changed by the user using a drop down
* I want to have the same various cells show dropdowns that are also based on the value of a cell which has a fixed value per column (in other words, a header).

I have tried a lot of different approaches, including using the INDIRECT function, named ranges, dynamic ranges using a table, INDEX and MATCH. All have failed.

The main issues I see have to do with the fact that my data has several possible values for each given header and classification. Also all values are not unique.
The best result I have ever got returned a dropdown list based on the header bot returned the entire column rather than limited to the classification values. So for example I had all instances of 'Main Colour' to choose from, not just those of the classification value.

I attach a sample file.

Any help very welcome.

Thank you.

**mikeTRON** · 08-12-2014, 01:59 PM

So without even looking at your sample workbook, you have highlighted a big issue. You want unique dynamic dropdowns but you dont have the data split out in a unique way.

So how do you want to overcome this?

**Pourradass** · 08-13-2014, 06:22 AM

Hi Mike,

Thanks for your reply.

That is a valid point. Each classification-header combination is unique. The issue I am faced with really is that those combinations are not intersecting within a single cell but several. In other words, and to take an example, the 'Bed' classification can take several values for its Main colour.

I have tried to spread the classification value within the data (not the table) for each row to allow for each possible combination but that didn't get me anywhere.