I have a dropdown in all the cells in a column. Around 150 rows. The four options inside the dropdown come from another tab (sheet), A1 to A4.

This works fine. The options are listed from that tab, A1 to A4.

The whole point of doing this was to be able to edit the values in A1 to A4 in another tab. If we edited these values, we'd like the dropdowns to auto-update in the main sheet. But it doesn't work this way. The data options now become invalid. Why? How can we overcome this? If this basic thing doesn't work, what's the advantage of having options pointed to a range instead of manually entering them in text inside the cell?

Welcome any pointers.


1 Answer 1


You can try the following:

On the cell, choose data validation and then "List from a range". Type the name of the sheet, followed by an exclamation mark and the cells that you want to take the answers from.

For example, I want the list to be updated based on sheet X and row A1 to A5: X!A1:A5

  • 1
    This does not fix existing values that used data from the range before it was modified.
    – GoodDeeds
    Jun 22, 2020 at 19:42

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.