Hello dwr,
I have a method of finding links to other spreadsheets within a
Microsoft Excel document, but the version that I used to test this was
2002. Hopefully the solution will also work with the version of Excel
that you are running.
1. Open up the Spreadsheet you want to remove the link from.
2. Click on Edit and then click on Find. You can also press CTRL-F to
bring up the “Find and Replace” box.
3. Enter *.xls into the find box.
4. Click on the "Options > >" button.
5. Change the "Within" to "Workbook" (so it looks through the entire
Workbook and not just the sheet you are looking at).
6. Click on Find Next. The cell it takes you too has a link to another
excel file.
7. Click on Find Next. It will continue to search through the document
for other excel files. You can also click on “Find All” to get a list
of all cells which link to other excel files.
If you have extensive linking within your workbook from one sheet to
another, it would be better to search for something such as "C:"
(which would give you all links on your C drive). Be careful not to
erase any fields that link within your worksheet, and only erase those
that link to another file.
Please let me know if you require clarification regarding this answer
or if you run into any problems using the above solution. I will be
happy to further assist you in a timely manner.
tisme-ga
Search (Solution) Strategy:
I searched Help inside Excel for ‘link spreadsheet’
From Microsoft Excel Help (Version 2002):
“Formulas with links to other workbooks are displayed in two ways,
depending on whether the source workbook — the one workbook that
supplies data to a formula — is open or closed. When the source is
open, the link includes the workbook name in square brackets, followed
by the worksheet name, an exclamation point (!), and the cells that
the formula depends on. For example, the following formula adds the
cells C10:C25 from the workbook named Budget.xls. Link:
=SUM([Budget.xls]Annual!C10:C25)”
“When the source is not open, the link includes the entire path. Link:
=SUM('C:\Reports\[Budget.xls]Annual'!C10:C25)”
As you can see, in both cases the link includes a file name directly
followed by the ‘.xls’ file extension. The search in the instructions
above takes advantage of this to find links to other Microsoft Excel
documents. |
Request for Answer Clarification by
dwr-ga
on
26 Mar 2003 19:11 PST
I am using Microsoft Excel 2000. I had initially tried your what you
suggested but Ctrl-F can't seem to locate the cell. Is there a way to
do this in excel?
|
Clarification of Answer by
tisme-ga
on
26 Mar 2003 19:24 PST
Please open up Excel before trying CTRL-F. It would be better to open
up Excel, and inside Excel click on Edit and then click on Find.
before continuing to Step #3.
I await your reply,
tisme-ga
|
Clarification of Answer by
tisme-ga
on
26 Mar 2003 19:34 PST
This is a completely different solution that should also work if you
are having problems with the previous solution:
Be sure to have a backup before attempting this.
1.Open up the spreadsheet.
2.Click on Edit and then click on Links...
3.You should see a list of all links in the file. (It does list all
links in the file).
4.I recommend that you click on "Break Link" for all of them except
for the ones where the "Source" is the exact filename of the document
that you have open. It will ask you if you are sure. Click Yes.
Thanks for the heads up on this method hellodolly.
tisme-ga
|
Request for Answer Clarification by
dwr-ga
on
26 Mar 2003 19:35 PST
To clarify my previous statement, I have opened the file in excel and
pressed Ctrl-F. However, I am using EXCEL 2000 and do not have the
option to change the "Within" to "Workbook". I tried selecting all of
the worksheets and I pressed Ctrl-A, effectively highlighting the
entire file, unfortunately it still can't locate the link.
|
Clarification of Answer by
tisme-ga
on
26 Mar 2003 19:39 PST
Hello dwr,
Thank you for explaining. The solution should still work, but you
might need to search through each sheet inside the file seperately.
Please try this, and also try the alternate solution I mentioned in
the previous clarification.
In the meantime I will research how to do this inside of Excel 2000.
Please update me if you are able to get it working, or let me know if
anything else comes up. I will likewise get back to you as soon as I
can.
tisme-ga
|
Clarification of Answer by
tisme-ga
on
26 Mar 2003 19:50 PST
Hello dwr,
I managed to find someone with Excel 2000 on their computer and will
be able to access it directly if we are unable to resolve this
tonight.
I have confirmed that "NOTE: The Break Link command is not available
in Microsoft Excel 2000 or earlier versions. Excel 2000 and Excel 2002
share the same file format."
SOURCE: http://support.microsoft.com/default.aspx?scid=kb;en-us;295970
I have a question for you: Do you know what cells the formulas are in?
There are problems with Excel refusing to let one delete linked files,
but I need to know if you have located the cells before we work on
this.
In the meantime:
-make sure that the file you are trying to change the link to has
fewer than 122 characters.
Here is also a brand new method we can try:
Search defined names that refer to external workbooks and either
delete the defined name or change the name to refer to a static string
or value.
On the Edit menu, click Find.
In the Find what box, type an exclamation point ! (nothing else but
the !) and then, in the Look in box, click Formulas.
Click Find Next.
Excel searches for any formula that contains an exclamation point,
which indicates an external or remote reference.
To delete the link and its results entirely, selecting the cell that
contains the link, and then click Clear on the Edit menu
You might need to do this more than once.
tisme-ga
|
Clarification of Answer by
tisme-ga
on
26 Mar 2003 19:59 PST
Hello dwr,
I received your latest comment and want to confirm that you have tried
this before I continue researching:
On the Edit menu, click Find.
In the Find what box, type an exclamation point ! (nothing else but
the !) and then, in the Look in box, click Formulas.
Click Find Next.
To delete the link and its results entirely, select the cell that
contains the link, and then click Clear on the Edit menu.
You might need to do this entire process more than once.
If you are unable to find any formulas with an ! in them, or with
*.xls that suggests that there are no external files that your
spreadsheet is linking to. In this case, I would like to know what
message you are getting that is telling you that you have other linked
excel files within the document?
tisme-ga
|
Request for Answer Clarification by
dwr-ga
on
26 Mar 2003 20:02 PST
To clarify, I do not know where the link(s) are located...trying to
find the cell(s). The file I'm in has much more than 122 characters.
Also, I can't search on the "!" b/c my file has tons of links to other
work sheets which contain a "!".
|
Clarification of Answer by
tisme-ga
on
26 Mar 2003 20:11 PST
Hello dwr-ga,
I am glad leskowitz-ga's solution worked for you!! You will be unable
to compensate leskowitz however because he is one of our customers who
was helping you out with a solution.
I was not ready to give up on this and would probably have come to a
solution, but leskowitz really helped out in finding this solution.
tisme-ga
|
Request for Answer Clarification by
dwr-ga
on
26 Mar 2003 20:16 PST
So leskowitz-ga works for you? Sorry, I'm just new to this and want to
do the right thing. For my own edification, how does your enterprise
work? By the way, I really do appreciate all of your hard work and I
don't doubt that we would have come to an answer shortly.
|
Clarification of Answer by
tisme-ga
on
26 Mar 2003 20:19 PST
Hello dwr-ga,
No, leskowitz-ga is a customer (on Google Answers) just like you! :)
Anyone is free to post comments and leskowitz-ga is a person who has
also asked questions before on Google Answers.
At this point, your question is answered and there is nothing
additional that you need to do. All the best,
tisme-ga
|
