Changing a drop down cell selection if the list changes

[Peter Davison]

I think the answer to my question will be no it cant be done without vba, but here goes.
I have a drop down list of say four selections as a list in Cell B4
Department
Sub Category
Size
Sales

The user has selected Sub Category in Cell B4
However the file has updated through many formulas and Sub Category is now Sector
Because the selection in the Cell B4 was Sub Category this is what remains in the cell despite the list not now having that option as a selection
So many analyses that used Sub Category will now show N/A
Is there any way the cell can retain whatever was in position 2 of the list so instead of Sub Category it puts in Sector?

I'm guessing it can only be done with a macro?

Any help would be great.
Thanks

 

[MedicineVan]

Would Replace work for you here?

Ctrl H
Replace Sub Category with Sector
Replace All

 

[Peter Davison]

Yes I did think of this, I believe I would have to do this by each individual sheet though.
Definitely worth considering.
Thank you for your help

 

[MedicineVan]

No problem : )

In the menu that pops up if you click on Options you can select it to affect the whole workbook.

 

[MedicineVan]

 

[Peter Davison]

That's great thank you