Indirect validation list with blank in list

[RT]

Normally when you create a named validation list that contains a blank line,
you can choose from the list or enter anything at all because of the blank.
I have a scenario where I am using an indirect validation list based on the
entry in the cell next to it. The named list contains a blank entry but you
get an error message when you try and enter something that is not on the list
even though their is a blank line contained in the named validation list.
For example, validation list is called employee_profile. If I do "normal"
validation using the formula =employee_profile for my validation list, I can
select from the list or type anything as there is a blank line in the list.
If I do indirect validation using this formula =INDIRECT(SUBSTITUTE($a1,"
","_")), where $a1 = employee profile, I cannot enter a value that is not on
the list, even though there is a blank line in the list.
Can anyone help?

 

[RagDyeR]

*Uncheck* the "Show error alert" that's in the "Error Alert" tab of the Data
Validation dialog box.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Normally when you create a named validation list that contains a blank line,
you can choose from the list or enter anything at all because of the blank.
I have a scenario where I am using an indirect validation list based on the
entry in the cell next to it. The named list contains a blank entry but you
get an error message when you try and enter something that is not on the
list
even though their is a blank line contained in the named validation list.
For example, validation list is called employee_profile. If I do "normal"
validation using the formula =employee_profile for my validation list, I can
select from the list or type anything as there is a blank line in the list.
If I do indirect validation using this formula =INDIRECT(SUBSTITUTE($a1,"
","_")), where $a1 = employee profile, I cannot enter a value that is not on
the list, even though there is a blank line in the list.
Can anyone help?

 

[RT]

Funny, easy solution. Thanks. I was just confused as you can have a blank
line in your list when you don't use indirect validation and you don't get an
error message, but when you use indirect, you get one. Oh well, no matter,
that works. Thanks!

 

[RagDyeR]

You're welcome, and thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Funny, easy solution. Thanks. I was just confused as you can have a blank
line in your list when you don't use indirect validation and you don't get
an
error message, but when you use indirect, you get one. Oh well, no matter,
that works. Thanks!