amend text in cells depending on date

[LiamF]

Hi, I have a list of stores with the same ID but different name. The enclosed spreadsheet has Store No., Store Name and Date.

I need the Store Name to be consistent and the latest name to be the default. ie. the Store Name for Store No. UK1 should read LONDON instead of LDN.

Is there a formula that can do this?

thanks
Liam

[Portuga]

You can try using the "find and replace" function.

Or maybe create a list with the correct codes for the each store stores and use a vlookup to your data using this list.

[NBVC]

Try this formula in D2:

Please Login or Register  to view this content.

it must be confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down.

You can then copy / paste special>> values over your original column and delete this new column.

[LiamF]

Try this formula in D2:

Please Login or Register  to view this content.

it must be confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down.

You can then copy / paste special>> values over your original column and delete this new column.

thanks, when I try to copy this down I'm getting the "you cannot change part of an array" message.

NB. I can't do search and replace as there's hundreds of stores where the name has changed slightly.

[NBVC]

thanks, when I try to copy this down I'm getting the "you cannot change part of an array" message.

Try deleting that column and try re-entering the formula in D2. Then confirm it with CSE keys and then copy it down.

see attached...it should work.

[LiamF]

yeah that's worked. Many thanks.

I think the problem was I was trying to copy it on top of D2 as well as a range of cells below it.

regards
Liam

[NBVC]

yeah that's worked. Many thanks.

I think the problem was I was trying to copy it on top of D2 as well as a range of cells below it.

regards
Liam

That's what I thought too