Data Validation Not Allowing Formula

[excelbytes]

Is this formula allowed for a List in Data Validation? It works fine outside of Data Validation, but won't let me include it within:

=IF($C$4<>"NDIA",INDIRECT($C$4&"_item"),OFFSET(NDIA[[#Headers],[Description]],MATCH($B$8,NDIA[Description],0),1,COUNTIF(NDIA[Description],$B$8),1))

I get the "There is a problem with your formula" dialog box pop up.

 

[mart37]

Make a new range name (myList) with: =OFFSET(NDIA[[#Headers],[Description]],MATCH($B$8,NDIA[Description],0),1,COUNTIF(NDIA[Description],$B$8)
Use the range name for your list: =IF($C$4<>"NDIA",INDIRECT($C$4&"_item"),myList)

 

[jasonb75]

Is this formula allowed for a List in Data Validation? It works fine outside of Data Validation, but won't let me include it within

There are a few things that do not work in validation rules, table names being one of them.
If you need to use a table range then you have to use a workaround such as the one @mart37 has suggested.

 

[excelbytes]

Thanks, mart37, that works perfectly!