Drop-down lists suddenly won't work.

[Pat from Indy]

Here's my situation:
I have a 5-sheet Excel workbook where I have one sheet titled "Lists". These are where the drop-downs reside. I named them (Insert/Name/Define), and then on the individual sheets I added the drop-downs where necessary via Data/Validation/Allow List and inserting "='Name of List'". This worked fine. Suddenly, out of the blue, though, the drop-downs won't work on only ONE SHEET! When you select a cell, the little gray box with the down arrow appears in the corner, but none of the lists appear. The other sheets still work fine.

This workbook is used by approximately 6 other users, so possibly someone has changed something, but for the life of me I can't figure out what. I tried, as a test, adding the drop-down back into one of the columns, but I get an error message telling me that the source currently evaluates to an error. Compared to the other sheets, I see no difference in how I'm doing this function. Could it just be a 'bad' sheet or corruption of some sort?

Thanks for any ideas. p.s: I am NOT a power Excel user, so speak plainly!

 

[Juan Pablo González]

Are you by any chance using Excel 97 AND have the freeze panes option turned on ?

 

[Pat from Indy]

Thanks for the reply, but no, I'm on 2000, on an NT 4.0 machine.

Just to be sure, I tried disabling the freeze panes, but that had no effect (no 'good' effect, anyway).

I'm starting to wonder if I should just replace the sheet entirely.

 

[just_jon]

Any hidden rows/columns, or special formatting in the area?

 

[Juan Pablo González]

...and, are you sure the dynamic range is actually working ? when the list is generating an error, you can see the dropdown button in the cell, but nothing happens when you click it...

 

[Pat from Indy]

No. no hidden cells or strange formatting. This is a pretty straightforward (though wide) spreadsheet.

You've stumped me on the 'dynamic range' reference. Can you explain further? Yes, as you said, the button shows up, but no list appears when you hover over it. It's this way on the whole sheet; there are at least a half dozen lists spread throughout.

Thanks again for your suggestions so far!

 

[Juan Pablo González]

Sorry, I get carried away sometimes... :D when you go to Insert | Name, Define... and you select the list that you're having problems with, and you click once with the mouse, in the refers to box, do you see the 'marching ants' ? and, are they poining at the correct range ?