Data Validation List - Update Historical Cells with New Data Validation

[Tashat]

Hi All

I have a data validation list of organisation names on one sheet. On another sheet I can enter records for the different organisations each time I make contact with them. The problem I have is when an organisation changes their name. I want to be able to update the organisation name in the data validation sheet and then ensure the historical entries for the old organisation name are updated to reflect the new organisation name.

Is there a way of doing this? As there are many people using the spreadsheet, I cannot guarantee that each person would go back and search for the old entries to update them manually, so I'm looking for an automatic approach.

Also, there will be new organisations added to the list regularly, as my company makes contact with new organisations, so I want the data validation list to automatically expand.

Many thanks in advance

 

[Tashat]

Hi All

I thought perhaps my explanation above wasn't enough, so I've pasted below to help illustrate what I mean. Hopefully someone can help me. Many thanks

Book1
	A	B	C	D	E	F
1	On sheet 1			Data Validation Sheet
2	Organisation			Data Validation List of Organisations
3	Organisation 5			Organisation 1
4	Organisation 2			Organisation 2
5	Organisation 1			Organisation 3	If this organisation name changes to - "Organisation 3a", I want cells A8, A15 and A16 on sheet 1 and cells A 26 and A31 on sheet 2 to automatically update to read "Organisation 3A"
6	Organisation 7			Organisation 4
7	Organisation 3			Organisation 5
8	Organisation 4			Organisation 6
9	Organisation 6			Organisation 7
10	Organisation 6			Organisation 8
11	Organisation 8			Organisation 9
12	Organisation 9			Organisation 10
13	Organisation 9
14	Organisation 4
15	Organisation 10
16	Organisation 3
17	Organisation 3
18
19
20	On sheet 2
21	Organisation
22	Organisation 4
23	Organisation 5
24	Organisation 6
25	Organisation 3
26	Organisation 1
27	Organisation 4
28	Organisation 3
29	Organisation 5
30	Organisation 3
31	Organisation 9
32	Organisation 4
33	Organisation 4
34	Organisation 10
35	Organisation 7
36	Organisation 7
37
38
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A3:A17	List	=$D$3:$D$12
A22:A36	List	=$D$3:$D$12