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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
duplicate named ranges- how to detect, delete?
|
#3
|
|||
|
|||
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 | |
|
|