Indirect Formula to report content based on two previous columns?

[Amigo1]

I’ve recently made changes to my Validation sheet to allow for dynamic name ranges and future expansions in my data.

The problem that I’m having after trying several options is that the indirect formula found in my Source Data Sheet, Table A, Description Colum Data Validation, is not working. What I’m currently using is;

=INDIRECT(SUBSTITUTE(D16," ",)&SUBSTITUTE(E16," ",)&"List")

The formulas intent is to report back a specific list of descriptions based on the selections made by the user in the STAGE and CATEGORY columns.

Any help is appreciated and file is attached.

Many thanks

[micope21]

Hi

Worth look at this website http://www.contextures.com/xlDataVal02.html

Regard

[martindwilson]

indirect does not like dynamic named ranges
try
creating another name eg mylist
then make that =EVALUATE(SUBSTITUTE('Source Data'!$D$16&'Source Data'!$E$16," ","")&"listdyn")
then use =mylist in validation
note the workbook need so be xlsm to allow evaluate to work

[Amigo1]

thanks Martindwilson for the solution.