Excel 2010 Bug? Cannot remove data validation, please help

[pirdop]

Hello all,
I am trying ot edit an old *.xls file. For some cells there is a dropdown list(data validation) that has been entered, but when I opened it with office 2010 it simply doesn't work. The source list data is still there but the excel doesn't see it for some reason.
The only sign left that there was a data validation rule entered is that I can still see the little arrow for the dropdown list on the cells in question. The problem is that I even cannot remove it!
I tried everything including DATA->Data Validation-> dialog box opens-> Clear all! the arrow is still in the cell but there is now dropdown appearing.

if I try to overwrite the data validation rule by selecting the same source data Excel returns an error message saying that the source formula evaluates to an error?

Thanks for your help!

PS I tried to save it in *.xlsx format but nothing changes

 

[RoryA]

What is the source formula? Excel 2010 has slightly different rules about what is permissible as a DV source than previous versions did.

 

[pirdop]

=Overview!$S$2:$S$4

nothing complicated

problem is that it works if I input it but after I save it in xlsx and re-open it again it doesn't work anymore. It behaves exactly the same way as described in my first post

fist time I see such thing

 

[pirdop]

For anyone in the same boat: here is the solution -
I saved the file as *.ods to get rid of all dropdown lists and then back to xlsx. That worked for me, but it seems to be a bug in xccel 2010.

 

[RoryA]

Was Overview the sheet that the validation was on? If not, that's not a valid validation list in an xls format (you can't refer to other sheets directly)

 

[pirdop]

No the validation is on the next 20 sheets, on the "Overview" sheet is just the list with values!

You are right it is a problem with the *.xls format, but why it was not working in *.xlsx is still a mistery to me. After I saved it in ods and then in xlsx and then re-entered the data validation rules, all seem to be working