Need Help Using Data Validation and INDIRECT

Posted by Ben O. on July 06, 2001 8:48 AM

I'm using an INDIRECT formula as my Data Validation List source:

=INDIRECT(INDIRECT("'Client Lists'!A1"))

'Client Lists'!A1 contains a range formatted as text, "'Client Lists'!A3:A75" which is where the list is located.

The formula works, but when I change the A to a B or C, it gives me an error. I can't understand why, because the cell I want to reference, 'Client Lists'!A1, 'Client Lists'!B1, and 'Client Lists'!C1 all contain the same kind of information, worksheet ranges, and are formatted exactly the same. Is this a glitch in Excel?

-Ben

Posted by Scott S on July 06, 2001 9:26 AM

I don't know why this worked, but it did. If you go to your cells that have the range formatted (A1, B1 etc.) and insert another "'" at the begining, it should work. In your formula bar it should read "''Client Lists'!A3:A75" (w/o the "").

Posted by Ben O. on July 06, 2001 9:47 AM

Thanks, Scott. It worked. I would not have thought of that.

-Ben