Name Manager Fun in Excel 2010

[mellifluous]

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

 

[Fishboy]

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.

 

[mellifluous]

Thanks so much Fb! I'll give it a go now and I'll update this reply with the outcome - Cheers!

 

[mellifluous]

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

 

[Fishboy]

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!