Names on Destination Worksheet

[mtd081011]

We have a Purchase Order workbook where the first tab is a template. We copy that tab and enter data for various PO's as needed. One of our maintenance techs was producing PO's and now I am getting messages about names that exist in the Destination Worksheet and it ask me to rename them or keep them. Not sure what he did, but I need to eliminate them. I had done this once before, but do not remember what I did. Any assistance would be greatly appreciated.

 

[SkipVought]

hi,

We copy that tab and enter data for various PO's as needed.

Funny. As I read that statement, I said to myself, "THAT is a recipe for disaster!" Lo and behold...

In Excel 2007+ there is a feature called Remove Duplicates. But without knowing more about the structure and function of that sheet, I'd be loathe to suggest simply applying that blindly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

Thank you, both, for posting this; I learnt something (totally irrelevant but worth learning) as a result:

I don't often copy a complete worksheet within a workbook, but you made me wonder what would happen if I did so, and the sheet I was copying had a named range on it. There would then be two named ranges, one on each copy of the worksheet, and both with the same name?

No, no error, and Yes, two named ranges with the same name, but referring to different ranges. Scarily misleading. In Excel2003, incredibly misleading, because there isn't really a useful tool to see what's going on. In Excel2007, the name-manager explains that named ranges have scope, and that the scope of the original range is global, but the scope of the copy of the range is limited to its own worksheet. Presumably it takes priority over the global version on its own sheet.

Yup, I can see the logic, but before combining named ranges with copying worksheets, that's a piece of information worth knowing.

 

[mtd081011]

Thank you both, at least I have a starting point top work with.

 

[SkipVought]

BTW, the first instance of a name in a workbook is GLOBAL in scope: that is you can use that name on any sheet to refer to that reference.

Subsequent name instances are confined to the SHEET and must use the Sheet name in each reference as
[tt]
=Sheet5!MyName
[/tt]
where MyName is globally defined AND also defined on Sheet5.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]