duplicate named ranges- how to detect, delete?

[Keith R]

XL2003

I had an old workbook which I used as a template for new data; I dumped the
old data and pasted in new, changed the workbook name, added and deleted
worksheets, etc. I had named ranges in there, and didn't mess with them
because I didn't need them at the time.

One sheet that I didn't keep was a sheet that had a bunch of graphs (for
reference, the data sources for those graphs are set to the named ranges). I
finally needed the graphs, so I opened an old copy of the workbook and used
the worksheet move/copy option to add a copy of the graphs worksheet to the
newer workbook.

I then checked the named range from the page with the graphs on it, and it
pointed to data in the new workbook- but the graph kept giving me errors
even though the data source reference to the named range was fine (it
wouldn't graph the range, and when I put the cursor in the graph data source
reference and tried to exit, it didn't like the reference).

Eventually, I figured out that when I accessed the named range from the
newer graphs worksheet, it pointed to the correct data in the newer
workbook. But if I selected/checked the same named range from one of the
other sheets in the newer workbook, that /same/ named range gives me a
different reference (back to the old data workbook).

I conclude that when I copied/pasted in the worksheet with graphs, it must
have copied in a duplicate copy of all of the named ranges, and the graph
balked because it didn't know which one to use.

So my question- is there a way to automatically prevent duplicate named
ranges from occurring, to detect when they do occur, and if they do occur,
to easily figure out how to remove the duplicates? Also, is the fact that
this can occur in the first place a bug?

Thanks,
Keith

 

[Don Guillett]

edit>links>break links???

 

[Keith R]

Don- thank you for your suggestion.

When I break links, I can break links to all /other/ workbooks except the
one I copied the graph sheet in from. I select it, say break links,... and
it stays on the list of link sources. No error messages, it just stays on
the list.

When I select a cell on the new graph worksheet and go to Insert/Name/Define
and check range "A_" it shows as:

=INDIRECT("Main Data!$AO$" & Graphs!$A$4 & ":$AO$" & Graphs!$B$4)

which is correct. However, when I go into the Main Data worksheet (same
workbook) and go to Insert/Name/Define and check range "A_" it shows as the
following (I've shortened the filepath for easier reading):

="AllValidData!$AO$" & 'C:\GB\[RFTv6.xls]Graphs'!$A$4 & ":$AO$"
'C:\GB\[RFTv6.xls]Graphs'!

Which is actually two problems (I think)- First, that the _same name_ refers
to two different ranges in the same workbook, and second, that my filepath
is so long, the last bit of the formula was truncated on the second example,
and I don't know whether it is just a limit on what it shows in the named
range dialogue, or if the formula itself is truncated (fortunately that
doesn't matter at the moment, since that is the version I want to get rid
of).

I created a test copy of the workbook and was able to delete both "A_" named
ranges and recreate the one I wanted, but I have lots of named ranges in the
workbook, so I was hoping there was a way to either avoid the problem in the
first place, or, convince Excel to delete all the 'old' named ranges and
only keep the ones that are referred to from the graph worksheet. I guess
I'm surprised that Excel didn't either warn me that it was importing
duplicate names, or automatically ignore them (refuse to import them) when I
copied in the graph worksheet.

Thanks,
Keith