Data Validation and Indirect

Hi,

In A10 and C10 I have drop down menus. The list is from a range name in another worksheet Labour! (A10 is =Coats, C10 is =Surface)

I have been using the following formula with no problems.

=IF(ISNA(VLOOKUP(C20,Labour!$A$3:$L$12,HLOOKUP(A20,Labour!$B$1:$L$2,2,FALSE),FALSE)),,VLOOKUP(C20,Labour!$A$3:$L$12,HLOOKUP(A20,Labour!$B$1:$L$2,2,FALSE),FALSE))

I want to replace Labour! with two new worksheets (New! and Existing!). They have the same layout as Labour!
In B5 I have put a drop down menu containing a list of New and Existing.
I have tried the following formula.

=IF(ISNA(VLOOKUP(C20,Indirect($B$5&"!$A$3:$L$12"),HLOOKUP(A20,Indirect($B$5&"!$A$3:$L$2"),2,FALSE),FALSE)),,VLOOKUP(C20,Indirect($B$5&"!$A$3:$L$12"),HLOOKUP(A20,Indirect($B$5&"!$A$3:$L$2"),2,FALSE),FALSE))

This does not return anything, not even an error.

Is this because the range names are on Labour! ?
Can I use an Indirect function in the list source in Data Validation?

Thanks in advance for any help.

Matt

Hi,

You can certainly use indirect() function in data validation ...

for example ...

Please Login or Register  to view this content.

HTH
Carim

Thanks for the reply.
I've managed to get my formula working now.
Not sure I understand the Indirect in Data Validation, I'll post a more specific question under a new thread.