I have a spreadsheet where a particular group of cells has defined drop down values to choose from, using data validation rules to get the list from a seperate sheet.

How do I update all of the cells using this validation to use a new formula? (when changing the sheet/cells where possible values are stored)

To explain further; I have a document where some cells are validating w.r.t. a group of values in another sheet. When copying this 1st sheet to another file, I lose the validation that pulls info from sheet #2. Now, in this particular case the cells were all in one column - so I could select multiple and change data validation to refer to a different set of addresses.

However, what if the validating cells were scattered throughout a sheet, and I wasn't aware of their locations? Hence, what I am asking is given a particular validation, how could I update it in all cells that refer to it (of if this is not possible)

1 Answer 1


I'm not 100% sure what the issue is, but if it is a question of how to change the range of options in data validation:

When using Data Validation, the drop downs will update automatically if you change the source-list of values that appear.

The only time you will need to re-configure the validation is if the range of the possible selections changes, or you start using a different range/sheet to validate the input cells.


options = [1, 2, 3, 4, 5]

Drop down shows:

[1, 2, 3, 4, 5]

If you want to add a value (like 6), you will need to re-do the validation because this will change the range of the options.

  • I added more detail to clarify my question, its related more to your mention of having to re-do the validation (I want to know how to do this for multiple cells)
    – Alok
    Commented Oct 12, 2012 at 1:40

Your Answer

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

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