Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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)

share|improve this question
add comment

1 Answer

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.

Example:

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.

share|improve this answer
    
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 Oct 12 '12 at 1:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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