Circle Invalid

[Matt]

Hi folks,

I have a problem with a spreadsheet using data validation. The
validation uses a list with the indirect function and named ranges so
the list is evaluated based on the content of another cell and the
lists themselves are on another sheet. I have used this solution in
many spreadsheets with no problems before. My problem now is that it
just does not work on a particular solution... The list is presented
OK in the drop down, but the user can enter any information just by
typing, no alarms show, even though they are set to. Also the circle
invalid data does not show anything.

Am I loosing my mind here? This one I just can't figure out!

Thanks in advance

M@

 

[Bernie Deitrick]

Matt,

I have heard of a bug where if your named range for the list includes a
blank then it may allow any entry.

The workarounds were to

1 - put something in the blank cell or
2 - deselect the "Ignore blank" in the data validation's Settings tab

HTH,
Bernie
MS Excel MVP

 

[Matt]

Hi Bernie,

Yes I have come across what you describe, but this is not the problem.
There are no blanks in the lists. Also with the ignore blanks
option, although this works, it also shows blank cells as invalid, but
in this case a blank cell is actually still valid.

M@