A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

duplicate named ranges- how to detect, delete?



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2007, 01:41 PM posted to microsoft.public.excel.misc
Keith R
external usenet poster
 
Posts: 58
Default duplicate named ranges- how to detect, delete?

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



  #2  
Old June 1st, 2007, 02:19 PM posted to microsoft.public.excel.misc
Don Guillett
external usenet poster
 
Posts: 6,167
Default duplicate named ranges- how to detect, delete?

editlinksbreak links???

--
Don Guillett
SalesAid Software

"Keith R" wrote in message
...
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




  #3  
Old June 1st, 2007, 04:41 PM posted to microsoft.public.excel.misc
Keith R
external usenet poster
 
Posts: 58
Default duplicate named ranges- how to detect, delete?

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


"Don Guillett" wrote in message
...
editlinksbreak links???

--
Don Guillett
SalesAid Software

"Keith R" wrote in message
...
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






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.