Data Validation dropping the tab name

New Contributor

I have a spreadsheet with 15 tabs. One of these tabs is titled "Data" for 13 of the other tabs they have 20-30  cells with pull down menus for selecting data. These menus are created with Data Validation and pull from cells in the Data sheet. Often these data validation links loose the reference to the Data sheet and only pull the menu from cells in the current sheet. Over time as various people use this sheet (which is protected) different cells loose this reference. Sometimes it is just one cell other times there are multiple cells and rarely are they same cells that loose this reference.


Is there a limit to the number Data Validation drop down menus a spreadsheet can have? How do I protect these links. It is becoming increasingly frustrating to the users when they can't use the sheet as designed.

4 Replies
Are you saying the reference to the list for the data validation changes? Can you perhaps attach a sanitized sample file where one or more cells have been "damaged"? Please indicate which cell(s) are damaged and which work as intended.
I have a master file that is copied into individual folders for the engineers to use for a specific customer. It is while they are using this copied file that they find menus that don't work. Open the file and look at the Choke Tube - 1 tab. I added a note in O29 explaining the situation.
How do I attach a file???



Here is the test file....I think.

I would suggest to:
- Convert each range used for Data Validation into a table
- Give the data rows of the column in the table a range name
- Use the range name in the DV

In addition: DV does not work well with merged cells, I advise not to merge cells.