When you set up an in-cell drop down list using Data Validation that references an existing range of cells within your spreadsheet as the criteria, by clicking Data > Validation and choosing List from a range, a problem arises when you duplicate the sheet and want to rely on a more localised functionality.
As an example, see this screenshot of the Data Validation window on an original sheet:
The expected outcome is that cell
A1 will have in-cell drop down list functionality that will list the contents of the non-blank cells
B1:B8 as separate items in that list.
The problem shows up when you duplicate the sheet and expect the duplicate's cell
A1 to reference the new, duplicated sheet's
B1:B8 for items in its drop-down list.
In this screenshot, notice how the duplicate's Data Validation window is referencing the original sheet's
B1:B8 for its items, i.e.
Sheet1!B1:B8, rather than referencing a more local range, the duplicate's
A way to achieve the desired outcome is to manually choose the local, duplicate's range, but ideally, when a sheet's Data Validation is duplicated, it would not add the
Sheet1 and try to persist the reference to the original's range, and ideally one would not have to manually fix every Data Validation in the sheet, every time they make a duplicate.
To be clear: the duplicate's
B1:B8 will be different from the original's, and is why Google Sheet's decision to have duplicated Data Validations persist references to the original sheet would not work in my situation.
The question: How can I duplicate a sheet that has Data Validation as described, and have the range referenced in the duplicate be local, rather than automatically referencing the original sheet's range?
User 'Carrie Rhodes' had the exact same problem and described it here in a Google Docs Help Forum, though it did not yield any helpful answers.
The reason I mention that is in case my question was not clear enough, and you wanted to read the same problem worded differently by someone else.