Validation From List Not Working

[Erin Searfoss]

Hi All,

I'm using Excel 2003. On one tab I have a range named Cats with category
names. On all my other sheets I'm setting column F to use validation based
on this list. When I first apply the validation (i.e. Allow: List, Source:
=Cats) the drop down appears when in any effected cell, but erroneous entries
are still allowed. Then when I save and reopen the file the validation isn't
there at all (i.e. no dropdown list and no indication of any previous
settings in the validation dialog box.) I use this same method in a
different file at least once a month without an issue. Any idea what I could
be doing wrong? Thanks

Erin

 

[Gord Dibben]

If erroneous entries are being entered with no warning perhaps you have
Data>Validation>Error Alert>Show error etc. unchecked.

As far as the DV lists disappearing I don't have a guess unless you are not
opening the same workbook.


Gord Dibben MS Excel MVP

 

[Erin Searfoss]

It's definitely the same file. I've made some other changes each time, and
they are intact. Also the time on the Date Modified is correct.

The Show error alert... box is checked. In fact, at one point I created a
specific message to try to force the issue, but no luck.

Anyone else have any other ideas? Hmmm...

 

[Gord Dibben]

If you want to send me the workbook I will have a look.

Change the AT and DOT for my email address.


Gord

 

[Erin Searfoss]

Thanks for the offer. I did figure out why erroneous entries were being
allowed. I had a blank cell at the bottom of my validation list range so the
range would grow if the user added an item to the list. I entered a space in
this cell and that issue was resolved.

I still don't know why the validation would disappear each time I saved the
file. However, when I copied the relevant sheets to a new file to send to
you I could not recreate the problem. The validation is sticking in that
file so I moved all the other sheets over to that file. Everything is
working properly now. Interesting...

Thanks for your help.