Validated drop down list update throughout spreadsheet???

[danieldrmm]

A column is validated to a drop down list on another worksheet.
The information has been inputted into the cells using the drop down list.

The original data in the drop down list have been updated.

For example, the original list included:

Type 1
Type 2
Type 3 ...

Now the list has been changed to

Type 1: Skirting
Type 2: Lino Coving
Type 3: Vinyl Coving ....

When I go to the cells, the options of available drop down have been updated but the cells which have been already inputted with the original do not update. For example, the cell which says "Type 1" does not automatically to " Type 1: Skirting".

How can I ensure that the cells which have been inputted also update with the revised drop down list?

Thank you.

 

[Yard]

A few options spring to mind:

i) do a Find & Replace throughout your workbook as a one-off exercise
ii) add Conditional Formatting to your input cells to highlight if the cell's value does not appear in the validation list
iii) some VBA to be triggered when the source list is amended and to replace all relevant values

 

[danieldrmm]

Thanks very much Yard - perhaps I am starting from the wrong premis.
I am filling in an architectural finishes spreadsheet for over 2 hundred rooms and would like to be able to select from a number of finishes for each column. I thought the best way to do this was to set up a validated drop down list but any amendments become problematic. Could you/anyone suggest a better method.

Many thanks!!

 

[Yard]

You can't have data validation and subsequently change the validation rules - that's the problem you've encountered.

You could do away with data validation and just use conditional formatting to highlight data entry errors.

You could have a code for each finish type which will not change, and the description of that finish type can then be looked up; that way you can change the wording of the description, since the entered value will be the unique code.

 

[danieldrmm]

Thanks Yard,
managed to find another way around it using additional columns and Vlookup.

thanks for your help.
daniel