FWIW I experienced the same problem yesterday when I moved a worksheet
from one workbook to another. The cure was to move it back into the
source workbook and copy, instead of move, the worksheet. Then I deleted
the worksheet.
-Jim
--
Jim Gordon
Mac MVP
Co-author of Office 2008 for Mac All-in-One For Dummies
http://tinyurl.com/Office-2008-for-Dummies
"randy...@officeformac.com" wrote:
> This is a rather large spreadsheet with hundreds of charts. One of the linked files I absolutely recognize the file name as one could have linked to, but there are no formulas referencing it, and I can't break the links. The other file that is linked that I can't break I have never heard or seen before. <br><br>> <br>
> > On 12/17/09 1:40 PM, in article 59baf...@webcrossing.JaKIaxP2ac0, "randy...@officeformac.com" <randy...@officeformac.com> wrote: <br>
> > <br>
> > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a few files that when opened in Excel 2007 Windows show that no files are linked, when I open the file in Excel 2008 Mac and look in the EDIT | LINKS two external file links show up. But when I try to break them I cannot. I click Break Link and nothing happens. <br>
> > <br>
> > I have searched the entire worksheet for the reference to that external file and cannot find anything, my only guess is maybe a chart has a reference (I've noticed that searches for formulas don't give results in Charts. <br>
> > <br>
> > Thanks <br>
> > <br>
> > Randy <br>
> > You must certainly recognize the name of the sheets. Can’t you determine if a chart refers to them? What about internal names? Did you check there? Are there any active-x controls on the sheet? Do you get any other errors? <br>
> > <br>
> > -- <br>
> > Bob Greenblatt [MVP], Macintosh <br>
> > bobgreenblattATmsnDOTcom <br>
> > <br>
> >
> .
>
I did a lot of debugging on the problem and eventually solved it.
Like Randy, I have a workbook that has multiple sheets. When opened with
links updated it wants access to an old spreadsheet that it should not be
concerned about. The Edit->Links->Break Link seems to have no effect.
Like Randy, I searched the formulas across the workbook for the name of the
offending spreadsheet but found no such link.
What I found, after a lot of work, was that the offending link, was not
directly coded but came from a hidden named range.
If I choose Insert->Name->Define it shows me all my named ranges and none of
them use the offending link. However, persevering, I deleted the names one by
one and I found a discrepancy.
It seems that while the list of names given with Insert->Name->Define (I'll
call it the "Name View") does not have any duplicate entries, duplicate
entries are in existence hidden inside the workbook mechanics. I don't know
where, just that they are held somewhere internally. The evidence is that
when I delete a name from the Name View, if it has no duplicate then it
disappears from the list. However, if it has a duplicate, after the name is
deleted it stays in the list but now showing the duplicate and pointing to a
different range or a different link.
I deleted all my names from the Name View, making a list of any names that
were invalid references or which had duplicate entries. When finished, I
closed the workbook without saving it and re-opend it. This time I went to
the Name View and deleted only those names that I had accumulated on my list
as being in error.
When I had finished, I saved the workbook, closed it and reopened it. The
phantom link had gone. I had to reestablish the correct named range for the
duplicate links found and deleted since both the correct and duplicate
entries were deleted.
If you have not too many names in your workbook it might be quicker to just
delete all the names, save the workbook, close it, open it and redefine the
named ranges. I had a lot and decided not to go this route.
I'm not sure if all this saving, closing and reopening is necessary. I did
it to be sure.
So that's what worked for me. It seems that an Excel workbook can contain
multiple occurrences of a named range. The Name View shows only one
occurrence but will display the hidden occurrence if the first one is
deleted. In this way, the hidden links can be found and deleted.
Hope it works for you.
....Jeff
"randy...@officeformac.com" wrote:
> This is a rather large spreadsheet with hundreds of charts. One of the linked files I absolutely recognize the file name as one could have linked to, but there are no formulas referencing it, and I can't break the links. The other file that is linked that I can't break I have never heard or seen before. <br><br>> <br>
.....Jeff
A good portion of you've "discovered" � and gone to a great deal of trouble
to confirm � is a 'feature' of Excel which has caused problems for eons
:-)... It is entirely possible to have the same range name used multiple
times in the same workbook. IOW, range names can be either Global or
Sheet-specific. (I can't comment on the impact this may have on 'Move or
Copy' but your finding certainly sounds possible.)
This can be a beneficial feature if used intentionally, knowingly &
implemented properly. My contention, though, is that it's too easy to do so
without realizing it � especially if more than one user works in the file
because there are no 'checks & balances' that apprise the user that a range
name already exists on other sheets. There is no *thorough* & explicit
documentation on on the repercussions it can cause (at least, I can't find
it if it exists). Most resources only describe how to create local range
names correctly, such as the info on Chip Pearson's site under the topic:
Global-Scope And Sheet-Scope Names;
http://www.cpearson.com/EXCEL/DefinedNames.aspx
What often happens, however, is that a workbook will have several similar
sheets, each for different Division, Location, etc. Those sheets will have
quarterly figures for various categories of values [such as Hardware,
Software, Accessories] specific to that group of records. A user will select
the range of categories & use Insert> Name> Create command on Sheet1 to
generate named ranges based on those categories. Or they may simply define
the names themselves based on labels common to both sheets. At that point
they are Global range names.
Then that user or a different user will use the Create command to create
range names on Sheet2. Absolutely no notification or prompt is generated to
indicate that those same names have already been used in the book. The
ranges on Sheet1 simply are "converted" from Global to Local on Sheet1 & the
new [duplicated] range names become Local to Sheet2.
Thanks for the detailed report. I'm certain that it will be helpful to
others & I intend to use it to help bolster my efforts to get the behavior
modified in time for the next release of Office.
Regards |:>)
Bob Jones
[MVP] Office:Mac
On 12/31/09 6:11 PM, in article
96DACCEB-D307-42F5...@microsoft.com, "jeffr"
--
Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom
Assuming you're still out there perhaps you've seen my reply to Jeff by now.
Unfortunately, your suggestion doesn't really help in this scenario. The
list that gets pasted only includes the ranges from the *first sheet* where
the duplicated names were created. It's only if the names were actually
created using "Sheet1!Hardware", "Sheet2!Hardware", etc. that they show up
as separate items in the list. The list doesn't even suggest the presence of
the second & successive ranges if they have simply been named "Hardware" on
each sheet, which is what happens when you use Insert> Name> Create... The
sheet names do not get appended to the labels being used.
Further, the existence of the additional ranges is only disclosed when you
are on those respective sheets. IOW, the list only includes any Global range
names plus the Local range names specific to that sheet... And if you select
multiple sheets the Paste command isn't available. The only way I've found
(other than programmatically, perhaps) is to go from sheet to sheet & either
paste the list for that sheet or look in the Define dialog (which I believe
is what the list is generated from). The Paste List dialog doesn't offer any
option or distinction pertaining to Local ranges on other sheets.
For example, if the name "Hardware" was created on Sheet1 first & Sheet2
second:
=SUM(Hardware) on Sheet1 sums those values in the range on Sheet1,
=SUM(Hardware) on Sheet2 sums the values in that range on Sheet2, but
=SUM(Hardware) on any other sheet in the book where a local range by that
name doesn't exist sums the values in the range named "Hardware" on Sheet1,
& if the range name was first defined on Sheet2 those are the values summed.
Call it a "bug", "by design", whatever. The fact is that it can have some
quite unexpected, undesirable & problematic results. IMHO, any time an
attempt is made to create/use an existing range name there should be a
notification, option and/or automatic updating of the pre-existing range
names to append the appropriate sheet name prefixes & convert them to local.
Regards |:>)
Bob Jones
[MVP] Office:Mac
On 1/1/10 10:31 AM, in article C7637F77.C0DF0%b...@nospam.com, "Bob
....Jeff
"Bob Greenblatt" wrote:
> .
>
Thanks for your reply. I followed the link you gave and it, together with
this thread, was a useful addition to my understanding of Excel links.
....Jeff
.....Jeff
Now, I see that choosing the names on the source workbook created new
sheet-specific names that were links back to the external source workbook
rather than locally to sheets in the destination workbook. Hence my phantom
link.
Now I know and I won't make that mistake again.
Thanks for helping me to get it straight.
.....Jeff
The easiest way to find the global names is to insert a new sheet and then
paste the names into that sheet.