Name Manager Fun in Excel 2010

mellifluous

New Member
Joined
Jun 3, 2015
Messages
3
Hey guys,

I've had a hunt around and I know there are some really good Name Manager solutions, but my one is a little bit strange.

I have a lot of duplicate names in the Name Manager.
This was from a rebuild when some LiveOffice components stopped working.

When I delete the Name Manager duplicates, a lot of my cells come back with #REF! even though I have correctly pointed the name range to the relevant cells.

So I can't seem to replace the Names like for like in the Name Manager and have the cells update automatically.

For example if I have several names called "SAUSAGESANDMASHNUMBERS" and that refers to cells A1 to A5 on a deleted sheet in the workbook and then I delete the duplicates and point that name to refer to A1 to A5 on the new sheet, the cells that I have that use the SAUSAGESANDMASHNUMBERS as a range in a formula now return #REF! ;)

Would really appreciate any help / shortcuts. At the moment I'm correcting them all manually and this is likely to take a couple of weeks at this rate! :):)

Thanks so much

M
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hey guys,

I've had a hunt around and I know there are some really good Name Manager solutions, but my one is a little bit strange.

I have a lot of duplicate names in the Name Manager.
This was from a rebuild when some LiveOffice components stopped working.

When I delete the Name Manager duplicates, a lot of my cells come back with #REF! even though I have correctly pointed the name range to the relevant cells.

So I can't seem to replace the Names like for like in the Name Manager and have the cells update automatically.

For example if I have several names called "SAUSAGESANDMASHNUMBERS" and that refers to cells A1 to A5 on a deleted sheet in the workbook and then I delete the duplicates and point that name to refer to A1 to A5 on the new sheet, the cells that I have that use the SAUSAGESANDMASHNUMBERS as a range in a formula now return #REF! ;)

Would really appreciate any help / shortcuts. At the moment I'm correcting them all manually and this is likely to take a couple of weeks at this rate! :):)

Thanks so much

M
Hi Mellifluous, welcome to the boards.

Personally I would try coming at this from a slightly different angle. I would find the named range that I plan on keeping and rename it (so in your example the SAUSAGESANDMASHNUMBERS range to be kept would now be something like SAUSAGESANDMASHNUMBERSNEW). The formulas tied in to the correct range should automatically update to the new range name. I would then delete out all of the duplicated named ranges called SAUSAGESANDMASHNUMBERS.

Once left with only SAUSAGESANDMASHNUMBERSNEW, I would do a standard Find / Replace for the whole workbook and replace #REF! with SAUSAGESANDMASHNUMBERSNEW. This should in effect tie all affected formulas in to the new named range.

Once this has been done, you can go back to the name manager and rename SAUSAGESANDMASHNUMBERSNEW to whatever you want and Excel should automatically update the range in your formulas accordingly.

I suspect there may well be a cleverer way of doing this using VBA, but you will need someone smarter than I to provide help with that.
 
Last edited:
Upvote 0
Works well, and a good argument for putting names in your name manager that are not part of one another if you're going to find and replace e.g. don't have SAUSAGES as one name and SAUSAGESANDMASH as another, unless you want a headache!

Thanks again FB, super helpful :)
 
Upvote 0
Works well, and a good argument for putting names in your name manager that are not part of one another if you're going to find and replace e.g. don't have SAUSAGES as one name and SAUSAGESANDMASH as another, unless you want a headache!

Thanks again FB, super helpful :)
Glad it all worked out in the end mate. Happy to help!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top