Indirect Offset Data Validation

Hello

I have a named range over a fixed range.
I have set up data validation using the indirect function to receive the name of my named range from another cell.
This produces the list of values in my named range as desired.
When I change the named range from a fixed range to use the offset function, the data validation no longer works.
Is there something about the offset function I am forgetting?


Thanks.

Hello

I have attached an example workbook.
Sheet1 Cell B2 has the dynamic, dependent, drop down lists using the method I have previously used with the indirect function inside offset function executed from the data validation list option. This works as expected.
Sheet2 Cell B2, tries to use the data validation list option to call the named range using the indirect function against the result of Sheet2 Cell A2. The named range is dynamic using the offset function.
I cant figure out why the example in Sheet2 does not work.

Regards,

Another option:
If you set the dynamic range as an actual table, you can use it with INDIRECT in data validation.
Create multiple tables, one for each item on the list, use each item as the table name. Excel will automatically create named range with the table name.
If there are spaces in the item, in the table name change it to underscore, e.g "United_States".
The formula in data validation:

Please Login or Register  to view this content.

Here's an example:

@StringJazzer
Thanks for reviewing my code.
There's another option which is using an UDF, without using actual Table.
Example:
Dependent data validation in A2:B7, the list in Country & City are dynamic.
How to set it up:
1. Insert this UDF in a code module:

Please Login or Register  to view this content.

2. Create a named range "COUNTRY", refer to: = 'SHEET2'!$A$2:INDEX('SHEET2'!$A:$A, COUNTA('SHEET2'!$A:$A), 1)
3. You must select the first dependent cell i.e B2 then create a named range "toXDAV", refer to: =xdav(Sheet1!$A2,Sheet2!$C$1:$Z$1)
4. Select A2:A7 create data validation > List > Source: =COUNTRY
5. Select B2:B7 create data validation > List > Source: =toXDAV

Note:
In sheet2, the headers of the list are actually on C1:E1, but I set it to $C$1:$Z$1 in the formula, so you can add more Country without having to change the formula.

Thank you for explaining that INDIRECT does not work with dynamic named ranges.
Thank you for the alternative solution. I like that this is concise and minimizes use of named ranges.
I noticed that the range inside the MATCH formula was not dynamic and did not work when adding new categories eg motorcycle, trailer etc. I simply replaced the range with your named range of "vehicle" and it worked fine.
Thank you for your help, this response answered my questions and more.