Data Validation List - Change and Auto Update Historical Entries

[Tashat]

Hi All

I have a list of organisations in a Data Validation List. This list is then used each time my colleagues make contact with the organisations. The issue I have is when an organisation changes its name from e.g. "Organisation 1" to "Organisation 1a", I want to update the Data Validation List (no problem with that), but also have all historical entries in the contact sheet to also update the name of the organisation to now be "Organisation 1a".

I know my colleagues won't remember to manually go back and change historical entries

 

[bebo021999]

Data source, try to keep "name 1", "name 2" in 2 columns.
Dropdown cell, next cell to test with name 1, if not found, test name 2.

Book1
	A	B	C	D	E	F	G	H	I	J
1
2									Company name
3	Dropdown	Name 2							Name 1	Name 2
4	ABC								ABC
5	DEF	DEF1							DEF	DEF1
6									xxx
Sheet4

Cell Formulas
Range	Formula
B4:B5	B4	=VLOOKUP(A4&"",INDEX($I$4:$J$6&"",),2,0)