data validation not working on one sheet

[Guest]

Excel 2003

I have a series of cells using a Named Range for a data validation list.
Until yesterday it worked fine. Now it doesn't work. The cell, list, range
is all the same. I've done the Edit/Goto/Special/Data Validation/All and it
highlights the right cells. I also tried to set up something very simpl:

A1,2,3 have values aa,bb,cc. I then set up b2 for data validation, list,
and drag the range over a1,2,3. Error alert is checked. Click on b2 and no
drop down. You can key in anything you want.

On all other worksheets data validation works just fine. Anybody have any
ideas what I can look for here?

thanks - Russ

 

[Guest]

Oh good lord. Problem closed. I see that the "=" disappeared prior to the
Range Name in the data validation source. That will do it. Sorry.

thx

 

[Guest]

I spoke too soon. Yes, the "=" was missing from the Source for data
validation. On my test page that solved the problem. When I added the "="
to my problem sheet, the problem persists. I still don't get any drop
down... Weird. any ideas?

 

[Gord Dibben]

No drop-down on just that one worksheet or just the one cell?

Maybe you have unchecked "in-cell dropdown" for that cell with the DV list.


Gord Dibben MS Excel MVP

 

[Guest]

Gord,

Data validation doesn't work in any way, just on this one sheet. I have in
cell drop down checked. I try source = named range, or just plain range and
it just doesn't work. I've created a new cell data validation and it doesn't
work either. Every other worksheet works fine.

thx

 

[Gord Dibben]

I'm not sure where to go from here.

I would suggest Tools>Options>View>Objects>Show all if it were the entire
workbook not showing the arrows, but with just one sheet............??

What happens when you select the cell and Alt + DownArrow?

Check out Debra Dalgleish's site for troubleshooting DV.

http://www.contextures.on.ca/xlDataVal08.html#Freeze


Gord

 

[Guest]

Gord,

Thanks for hanging with me on this one. I punted. Check this out...

If I create a copy of the worksheet, the existing DV fails just like the
original ws. If I copy the whole ws and paste it onto a new ws, the DV works
just fine. It was a pain in the butt, but that is what I ended up doing. I
copied to a new ws and then went about modifying all formulae tha refered to
the ws, Names, etc.

I wonder if a single ws can become corrupt?

Anyway, I'm done with it now. I've ported the cells and delete the old
problem sheet. HOPEFULLY I won't see this again, but I have a bad feeling...

thx