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

If erroneous entries are being entered with no warning perhaps you have
DataValidationError AlertShow 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

On Thu, 6 Aug 2009 11:22:01 -0700, Erin Searfoss
wrote:

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

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

If erroneous entries are being entered with no warning perhaps you have
DataValidationError AlertShow 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

On Thu, 6 Aug 2009 11:22:01 -0700, Erin Searfoss
wrote:

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

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

Change the AT and DOT for my email address.


Gord

On Thu, 6 Aug 2009 14:33:01 -0700, Erin Searfoss
wrote:

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

If erroneous entries are being entered with no warning perhaps you have
DataValidationError AlertShow 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

On Thu, 6 Aug 2009 11:22:01 -0700, Erin Searfoss
wrote:

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

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.

"Gord Dibben" wrote:

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

Change the AT and DOT for my email address.


Gord

On Thu, 6 Aug 2009 14:33:01 -0700, Erin Searfoss
wrote:

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

If erroneous entries are being entered with no warning perhaps you have
DataValidationError AlertShow 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

On Thu, 6 Aug 2009 11:22:01 -0700, Erin Searfoss
wrote:

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