Updating cells which reference a defined name

[Mike Miller]

I am using defined names as a validation criteria for cells in a
workbook. I have a worksheet that will contain the list of all
facilities and a seperate worksheet that will contain the realationship
between the facility and locations within the facility.

Example:

Defined Name Facility Code

ABC
DEF
EFG

Now I have a seperate sheed that will identify various things at the
particular Facilities

Example:

ABC -> Boardroom
ABC -> Cafeteria

What I want to know is if the code of ABC in my defined name is changed
to say ABC123, how can I automatically update the reference in the
worksheet that uses this defined name as a validation criteria?

Mike

 

[Debra Dalgleish]

You could do this with programming. There's a sample file here, that you
may be able to adapt to your workbook (test it on a backup copy):

http://www.contextures.com/excelfiles.html

Under Data Validation, look for "DV0022 - Update Validation Selections"