Drop down validation stopped working

[pancake]

I have an excel spreadsheet with lots of worksheets on. One of my users did something to it (She thinks it was related to a copy and paste) and now the dropdowns on the sheet have stopped working, and the cell appears as a normal cell without validation attached.

I have tried to re-create the validation, but it just won't do it. The spreadsheet is very complex and can't just recreate the sheet as I have tried, so need to get this working.

I have been looking at this for a couple of days now and am getting pretty desparate

Help !

Thanks in advance

 

[GlennUK]

Have panes been frozen on the sheet? If so, try unfreezing panes and seeing if that fixes things ( menu command Window/unfreeze panes )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pancake]

Thanks for such a quick answer

The panes were frozen initially, but aren't anymore. Not sure if my user removed this or when things went wrong it stopped freezing the panes. Either way this hasn't cured the problem.

I hope it is not a corrupt sheet, rather I am missing something obvious.

Thanks

 

[combo]

A drop-down validation list refers either directly to range or to named range or its own internal list.
The validation rules are stored behind the cell, and are cleared if you copy-paste from another range or remove cells.
So the only way is to re-create validation knowing the functionality of the application (unles you have an old copy).

combo

 

[SkipVought]

Hi,

"...She thinks it was related to a copy and paste..."

If you user COPIED into a validation cell, the Validation Format was lost, just as if you copied formatted range into range with a different format, guess what -- the copied format prevails!

Skip,

_[red][/red]

 

[GlennUK]

Oh yes Skip, I didn't spot that. My users are always doing that here.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[pancake]

Thanks to you both for your help

The thing is that now on this sheet I cannot recreate any kind of validation, even in cells that weren't previously used. It opens up the dialog box and looks like it will create the list, but the drop down arrow does not appear on the right hand side, however if I try to type anything other than the values it brings up the error as it should.

Thanks

 

[combo]

When setting list for validation, do you have 'in cell dropdown' checked (on the right)?

combo

 

[pancake]

Yes In Cell Dropdown is checked.
I was wondering if there was anywhere else that validation could be disabled Tools->Options for example ?

Thanks

 

[combo]

In Tools>Options>View tab: do you have 'display drawing objects' set to 'all' (or at least 'placeholders')?

combo

 

[yogia]

Hi pancake:

Combo is right.

if in Tools>Options>View tab: do you have 'display drawing objects' is set to 'hide all'

the validation for the cell works but the DropDown Arrow to the right is not displayed.

So look at the setting and change it to 'show all' or at least to 'show placeholders' and see if that restores the DropDown Arrow for you.



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[pancake]

Thanks combo and yogia, I have tried this and it was set to show all. I am coming around to the idea that the sheet is corrupt and may neet to put my excel head on to sort this out.

Thanks for your help.