Reflecting changes to Named Lists

sam_d1

Board Regular
Joined
Mar 17, 2002
Messages
50
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Have you tried find & replace method?
GNaga
 

sam_d1

Board Regular
Joined
Mar 17, 2002
Messages
50
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

Hi,

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?

Paddy
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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...
 

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851

ADVERTISEMENT

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;
=vlookup(City,OldNameList,NewNameList,False)

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.
HTH
BigC
 

sam_d1

Board Regular
Joined
Mar 17, 2002
Messages
50
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.

thanks
- sam
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.

Paddy
 

Forum statistics

Threads
1,144,059
Messages
5,722,272
Members
422,419
Latest member
Havok390

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 MrExcel.com.
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 "mrexcel.com".
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
Top