Reflecting changes to Named Lists


Board Regular
Mar 17, 2002
Assume that I am creating a list of addresses.

Appart from the other things, I also creat a named list of Cities (Delhi,Madras,Bombay ) and name it as "CityList"

I apply this list to the 'City' column of my address table. (through Data/Validation/List)

I have entered a few addresses.

Suddenly the Government decids to change the name of the Cities. (from Madras--> Chennai and Bombay-->Mumbai)

Now I want do this change only in my "CityList" list and I want this change to be reflected in all the addresses where I had selected Madras or Bombay.

Is this possible ? hope it is :(

Thanks in advance for your replies.
- sam

Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Have you tried find & replace method?
Upvote 0
Thanks for your reply...

I thought of this...

But this requires a bit of coding...

I need to,
1. Check if the chanded valuse was ever used.
2. Save the old Value.
3. Decide on the range to 'Find'
4. Then replace with te new value.

I still hope that, there must be some way in which excel will take care of this automatically.
- sam
Upvote 0

I don't see why the manual find - replace is out of the question here. Assuming that cities don't get their names changed that often, the find - replace all option would only take a few seconds...

Do you have another reason why you want to do it in code?

Upvote 0
On 2002-09-23 21:50, sam_d1 wrote:
Any Idea ?

If the old name has been selected from the data validation dropdown list and used somewhere, the 'user cells' will not be able to pick up the new name.

To see this, make a single item list:

In A1 enter: =TODAY()

Name A1 DList.

Set up data validation on B1 with DList as source.

Select in B1 the only item.

In C1 enter: =B1

Change the date in A1 to 09/24/2010 or wait until next day.

Observe C1...
Upvote 0
Copy the City column of cities entered in your database - to preserve the data before any changes are made.
Create a separate table (perhaps using your existing CityList as the first column) with "OldName" and "NewName" in adjacent columns (where no change, include same name in NewName column, so that all cities are included in both lists).
In a new column in your data base, use a VLOOKUP formula to search the OldName list for the current value stored for each record, and return the NewName, viz;

Copy the formula down for each address in the database => now you have a column of current city names.
Copy the vales from this column and paste special values over top of the "old" values in the City column.
This process is a bit clunky, but shouldn't take too long and will give you the result you're after.
Upvote 0
Hi all,

Thanks for all the replies...

But I had simplified the problem, for easy understanding.

The actual problem I am working on is, to creat a simple defect repository for small projects. (Why...? For small projects it does not make sense to creat/maintain an account in our regular Defect_Tracking_System ClearQuest).

In the excel file that I am developing, I have defect table in 'sheet1' (with ColumnHeads as 'Id', 'Title', 'Severity', 'Assigened_To' etc...)and the various Lists in 'sheet2' (say 'Severity_List', 'Team_Members_List', etc...)

When one of the team member leaves and another one joins (believe me... it happens quite frequently), the new engineer takes over all the defects assigned to the old engineer. So the defect table should reflect any changes made to the 'Team_Members_List'

This changes may happen even after the project team has entered a lot of defects in the table and I have no control over the excel files at this point in time.

So I need to make this automatic.

Hope this help you understand the REAL problem.

Thanks in advance for any help.

- sam
Upvote 0
Given the extra details, VBA is indeed the way to go, but we need more info on where your source list is, where the data is etc...

Post back with more info.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back