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.
Best Answer
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