Hello
Have a look at the file attached. I used 'Data Validation' to force cell B1 to accept just numbers 1 or 2 (if A1 be equal to the word "Iamgood").
If the word "Iamgood" (in cell A1) changes to "I am good", then it does not work. It seems spaced between characters make problem for it?
Let me know your ideas.
Thanks a lot.

Attached Files

Data Validation.xlsx (8.6 KB, 10 views)

You have defined (as defined name) iamgood, referring to E1:E2. And in your Data Validation you refer indirectly to iamgood (in A1) so of course if you change A1 to I am good then =INDIRECT($A$1) returns an error (#REF!).

xor
The problem is Data Validation does not accept Spaces between words as defined name and the error appears. Have a look at photos below and open the file again and try to change the defined name to "I am good" and see what happens.

Attached Images

	1.jpg (192.6 KB, 13 views)
	2.jpg (203.7 KB, 13 views)

Then what about using

=INDIRECT(SUBSTITUTE($A$1," ",""))

in Data Validation?

It done. Thank you very much.