Name manager duplicates entries - but corrupts the duplicates

[sgcannon]

A couple of times I've been working with name manager and inexplicably, it duplicates all my entries. One of each pair (I don't know whether it is the original or the duplicate) is corrupted. It seems to happen when I have two different versions of the same file open. I had hoped to post a couple of screen shots of what I'm talking about but I can't figure out how to upload images on Mr. Excel.

Is it a no-no to open name manager when two files are open?

Thanks,

Steve

 

[Jerry Sullivan]

Hi Steve, There isn't any reason not to open name manager when you have more than one file open.

In the name manager, do your duplicate have the same scope and workbook/worksheet references? It's not unusual to have the name duplicated with each listing referencing a different workbook or worksheet.
It would be unusual for duplicate name listings that have the same scope and workbook/worksheet references.

There isn't a post image files on this site. When needed, you can post to an image hosting site then provide a link. In this case, if you provide a few examples of the text appearing in the Name Manager for those duplicates (including Name, Refers to:, Scope), that should provide the information needed to help you.

 

[sgcannon]

Thanks for your willingness to help.

For example (I can't get the columns to stay organized when I submit the post so I just added ....)

Name...... Value..... Refers to.... Scope
Col_First..... 29...... ='Col Def'!$b$6.... Col Def
Col_First..... #Ref!...... =#Ref!#Ref!.... Workbook
Cstrk..... (...)...... ='C:\users\steve... .........Col Def (Refers to another version of the workbook)
Cstrk.... 50%....... =Master!$dp$3.... Workbook



Thanks!

Hi Steve, There isn't any reason not to open name manager when you have more than one file open.

In the name manager, do your duplicate have the same scope and workbook/worksheet references? It's not unusual to have the name duplicated with each listing referencing a different workbook or worksheet.
It would be unusual for duplicate name listings that have the same scope and workbook/worksheet references.

There isn't a post image files on this site. When needed, you can post to an image hosting site then provide a link. In this case, if you provide a few examples of the text appearing in the Name Manager for those duplicates (including Name, Refers to:, Scope), that should provide the information needed to help you.

 

[Jerry Sullivan]

None of those names represents an unusual duplicate.

For Col_First, in one case this has scope on Sheet "Col Def", in the other case, the name has workbook scope. The #REF! value isn't corruption per se- it's likely that the worksheet that was originally referenced by the name was deleted.

The Cstrk names have scope in two different workbooks, so there's nothing wrong or unusual with that.

When you copy a worksheet that has a named range with workbook scope, Excel will automatically create a new Name with worksheet scope on the new worksheet. You can copy the worksheet 10 times and end up with 11 Names that have the same name but different scope.

 

[sgcannon]

I think I understand what you're saying. And yes, someone else worked on Col Def which has named ranges in it. Then I deleted my Col Def and copied his version over (maybe not in that order). So that's why I have two sets of each named range, correct?

In the future, should I delete all his named ranges before copying his worksheet over so that I don't get the duplicates?

Thanks, you've been a ton of help!!!

Steved

None of those names represents an unusual duplicate.

For Col_First, in one case this has scope on Sheet "Col Def", in the other case, the name has workbook scope. The #REF! value isn't corruption per se- it's likely that the worksheet that was originally referenced by the name was deleted.

The Cstrk names have scope in two different workbooks, so there's nothing wrong or unusual with that.

When you copy a worksheet that has a named range with workbook scope, Excel will automatically create a new Name with worksheet scope on the new worksheet. You can copy the worksheet 10 times and end up with 11 Names that have the same name but different scope.

 

[sgcannon]

READ THIS ONE INSTEAD

I think I understand what you're saying. And yes, someone else worked on Col Def which has named ranges in it. Then I deleted my Col Def and copied his version over (maybe not in that order). So that's why I have two sets of each named range, correct?

In the future when I want to copy someone else's version of a worksheet that has named ranges, how is the best way to do that? I am thinking that I should delete all the names in my workbook that refer to Col Def. Then delete Col Def. Then copy his version of Col Def over to my workbook? Except maybe the name ranges will refer to his version of the workbook and so the links will be screwed up?

Now that I understand what is going on, I can manually correct the problems, just wondering if there is an easier way.

Thanks, you've been a ton of help!!!

Steved

 

[Jerry Sullivan]

I think I understand what you're saying. And yes, someone else worked on Col Def which has named ranges in it. Then I deleted my Col Def and copied his version over (maybe not in that order). So that's why I have two sets of each named range, correct?

Yes, the second set of names was created when you copied the worksheet (which would have had to have been before you deleted it.)

In the future when I want to copy someone else's version of a worksheet that has named ranges, how is the best way to do that? I am thinking that I should delete all the names in my workbook that refer to Col Def. Then delete Col Def. Then copy his version of Col Def over to my workbook? Except maybe the name ranges will refer to his version of the workbook and so the links will be screwed up?

Now that I understand what is going on, I can manually correct the problems, just wondering if there is an easier way.

If you really want to keep the Name Manager clean, then you need to delete any names that are scoped to or reference a deleted worksheet. That's probably easier to do after deleting the worksheet, because the values will show up as #REF!.

If you limit your cleanup to those names that reference a deleted sheet in the same workbook, that wouldn't affect some else's names that reference a different workbook.

FWIW, I typically don't pay that much attention to inadvertently created names in my workbooks. Occasionally, I'll review the name manager and delete unneeded names- but for the most part, they are harmless so I don't clean them up unless I'm handing over a file for someone else's use.

 

[sgcannon]

At least you've cleared up a mystery for me. I know where it is coming from now and how to fix it.

Would you mind helping me with one other similar matter? I use a lot of conditional formatting. Periodically I'll go into conditional formatting and find that what was once a block of cells with one conditional formatting range is now listed using ten ranges. Presumably this is because I've inserted cells or something similar. Of course it bugs me and I go in and redo the range. Is there a quick way insert cells and have the conditional formatting range stay the same, that is, having one range with the same beginning and end points but now including all the inserted cells?

Thanks for your help!

Yes, the second set of names was created when you copied the worksheet (which would have had to have been before you deleted it.)



If you really want to keep the Name Manager clean, then you need to delete any names that are scoped to or reference a deleted worksheet. That's probably easier to do after deleting the worksheet, because the values will show up as #REF!.

If you limit your cleanup to those names that reference a deleted sheet in the same workbook, that wouldn't affect some else's names that reference a different workbook.

FWIW, I typically don't pay that much attention to inadvertently created names in my workbooks. Occasionally, I'll review the name manager and delete unneeded names- but for the most part, they are harmless so I don't clean them up unless I'm handing over a file for someone else's use.

 

[Jerry Sullivan]

Debra Dalgleish has a nice blog entry on this topic on her Contexture's site.

http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/

The article explains how to avoid this problem, and how to fix it in existing workbooks (she recently added a link to a macro that can help with this clean up).

 

[sgcannon]

Thanks! That was REALLY helpful.

Steve

Debra Dalgleish has a nice blog entry on this topic on her Contexture's site.

http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/

The article explains how to avoid this problem, and how to fix it in existing workbooks (she recently added a link to a macro that can help with this clean up).