Indirect Validation after using SUBSTITUTE on a Dynamic Name Range NOT WORKING

[Jason Eric]

Hi Guys!
I am trying to use an Indirect Validation using substitution that will direct me to a dynamic name range. But this is just not working.
This is my DV code:
=INDIRECT(SUBSTITUTE(A2&B2," ",""))

I have 54 dynamic name ranges in Sheet 1.
Sheet 2 has Data Validation Lists.

Now, on Sheet 2, in address C2, i want to take the texts in A2 and B2, join them (that's why the SUBSTITUTE(A2&B2)), and direct this to a Dynamic Name Range so as to give me the menu in that range..

But this code doesn't seem to work. It only works if the Name Rane is NOT Dynamic...

Can somebody please help me...

The attachment has the Dynamic Named Ranges on Sheet 1 and the Dv lists on Sheet 2.

Thanks a lot,
Jason

[benishiryo]

hi Jason Eric. yes, you can't have dynamic Named Range & INDIRECT together. here's a workaround by Jerry. see if it helps:
http://www.excelforum.com/excel-tips...t-problem.html

[Jason Eric]

Thanks so much

[Jason Eric]

I managed to work it out without using Dynamic Name Ranges.....