I have a Google Sheet with multiple tabs in it. One for every week of the year. On one of those tabs, called MasterList, I also have a list of items from A1 to A50. I've created a dropdown of those items and it works as expected. When I copy that same dropdown and paste it into another cell in MasterList, it also works as expected.

When I go to one of the other tabs, Week1 for example, and setup a data validation rule for cell C4, I use the criteria of =MasterList!A1:A50. This works as expected.

I want to then use the same criteria for C4 through C51 on the Week1 so that basically I have an entire column of dropdowns that allows me to select one of the options created in MasterList. I would also then do this for columns D through I.

The issue I'm running into, is that when I copy cell C4 and paste it into C5, I lose 1 of the list items. When I paste into C6, I'm missing 2 of the list items. I know that this is happening because the criteria is changing to =MasterList!A2:A51 and =MasterList!A3:A52 respectively. Is there a way to add a list to all of the cells I mentioned above without having to modify the criteria in every single one of them?


1 Answer 1


A friend actually helped be with the answer, which is embarrassing because sheets also gives you the answer right in the criteria section of the data validation.

I updated the criteria to "=MasterList!$A$1:$A$50" and now it works regardless of where I paste the copied cell.

[1]: https://i.stack.imgur.com/ISvNj.png

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.