Updating Pick List

[foxguy]

I have a list of Product Names that I use in pick lists (DATA>VALIDATION>LIST).
Sometimes the company will change the Product Name.
I would like to have every cell that has that Product Name selected to automatically change to the new value.

Example

Please Login or Register  to view this content.

Cell B22 is Validated List>Product Names
A cell has selected "Lemon Bar" (and displays "Lemon Bar").

The company changes it to "Lemon Mousse Bar". I would like cell B22 to automatically change to the new name.

The only thing I can think of is to have the Worksheet_Change() event use Lookup to find the Product Code and store it in a separate cell (Q22) then in another cell (R22) have a Lookup find the Product Name. Then when the Product Name is changed in the list at least cell R22 would be accurate. I could manually go back and re-select the product in B22 at a later time so that it matches R22. Seems like there should be someway to do it without using VBA, and not have to duplicate the Product Name.

[MarvinP]

Hi foxguy,

In Microsoft Access this feature is called Cascading Updates. It is used when someone changes their name and you want to update all the old maiden names to the new one. See http://office.blogs.webucator.com/20...t-access-2010/

I think your best bet is to do a Search and Replace manually. I don't see how you could hang this on an event macro.

[foxguy]

It did occur to me after posting that I can do the whole thing at once in the Worksheet_Change() event. I would still have to store the Product Code somewhere (Q22). I would just have the event macro do a Lookup() and replace the Pick List value. I would still like a way to avoid using VBA if possible though.

[MarvinP]

Hey,

Not using VBA? I still think a manual Find and Replace in a Column or the whole worksheet is the non VBA answer.

[foxguy]

The file doesn't belong to me. I'm trying to make it easy for the employees. It would be unacceptable to expect every new employee to know how to use Find & Replace.

[snb]

Your mutually excluding requirements prevent any solution, let alone the contradictory ones (not using VBA, but using a Worksheet_change event).

[foxguy]

snb;
You mis-read my message. I don't want to use VBA, but the only solution I can think of uses Worksheet_Change() event. I included it so that it might give someone an idea I hadn't thought of.