Dynamic Data Validation with Multiple Criteria

Hi all,

I'm a new member so please bare with!

Please see attached.

I am looking to create Dynamic Data Validation on the sheet 'RAD Selector' in Columns K, L and M.

I want the selections to automatically update when any data is added to the RAD data table on the 'RAD Data' Sheet.

I currently have manually set this up with named ranges, but would like to automate if possible.

The difficulty I am having is reducing down the data to the unique entries for each Range, Type and Height.

They very for each Range and Type, hence the need for it to filter out those that aren't relevant.

I can figure out how to do this for one cell, using spill ranges.

However, I need this process to occur on each row.

I have tried a range of things but have been unable to get this to work.

I hope this make sense, along with the attached.

Any help would be greatly appreciated.

Maybe this:
I'll show an example for 1 and if that works for you, you can expand it to the rest. Let's look at RAD Range = LST

For your dynamic drop-down, in a cell L5, say, enter this:

=UNIQUE(FILTER(RADData[RAD Type],RADData[RAD Range]=L$4))

Change your named range so it references the dynamic list like this:

='RAD Data'!$L$5#

(See attached). Is this what you're trying to do?

Hi Greg,

Thanks for your response and help so far.

That is along the right lines, but I am trying to put the Data Validation in column L on the RAD Selector Sheet.

The RAD Data sheet is just where the Data that the formulas are looking at is - nothing will be changed here.

I had already tried the Unique Filter formula (=UNIQUE(FILTER(RADData[RAD Type],RADData[RAD Range]=K9))), and it works - creating a spill range. However, when putting this into the Data Validation (Allow: List, Source: (Above formula)), it gives me an error - below:

"Not trying to type a formula?
When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula:

• you type: =1+1, cell shows: 2

To get around this, type an apostrophe ( ' ) first:

• you type: '=1+1, cell shows: =1+1"

I believe this could be because I am using the Table Name, rather than Named Ranges. However, I have created Named ranges and that gives me the following error:

The Source currently evaluates to an error. Do you want to continue?

On the RAD Selector sheet, I basically need column L to depend on what is entered in column K, returning the unique RAD Types for the RAD Range. I then need column N to depend on both entries (Column K & L), returning the unique RAD Heights for the selected Range and Type.

Hope that makes more sense!

For some reason, the DATA validation entry does not like the formulas.

I have it working, creating spill ranges, but when putting into the data validation, it just gives me an error.

These are the formulas:

1 - =UNIQUE(FILTER(RAD_Type,RAD_Range=K9))

2 - =UNIQUE(FILTER(RAD_Height,(RAD_Range=K9)*(RAD_Type=L9)))

I cannot get the data validation to look at a cell/spill range for the options because I need to be able to make different selections on each row.

Hi,

Thank you for your help.

Unfortunately this isn't quite what I am looking for.

I need the Data Validation to be dynamic and automatically update/add new entries when new data is input to the RAD Data table!