Forum Discussion
Cell Reference instead of Text
I am attempting to set up a Data Validation scenario which uses the INDIRECT function, but am having a bit of issue. I need the headers of the items in the "Create from Selection" to be CV8 and CV9, but just text of it and not cell references. When I put in other items (such as words) to the headers, the INDIRECT function in the Data Validation set-up works fine, but as soon as I make the headers CV8 and CV9, then the drop down list is blank.
I have attempted to add a single quotation mark before CV8 and CV9 to make it understand it is text and not a cell reference, but that didn't work. I have attempted to make the cells formatted as both text and then number, that didn't work. I'm sure it's an easy fix, but I don't know how to do it. Any help would be greatly appreciated. Thank you.
1 Reply
If you use INDIRECT(...) where ... evaluates to CV8, Excel will interpret it as the cell CV8, regardless of how you format ...
You might name the ranges CV8_ and CV9_
If your drop down listing CV8 and CV9 is in - for example - D2, the data validation formula could be
=INDIRECT(D2&"_")
That would allow you to keep on using CV8 and CV9, while avoiding the cell reference problem.
