Indirect validation list with blank in list
 

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?

Indirect validation list with blank in list
 

*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!
================================================== ===



"RT" wrote in message
...
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?

Indirect validation list with blank in list
 

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" wrote:

*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!
================================================== ===



"RT" wrote in message
...
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?

Indirect validation list with blank in list
 

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

Regards,

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

"RT" wrote in message
...
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" wrote:

*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!
================================================== ===



"RT" wrote in message
...
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?