3

I have a huge spreadsheet, after some copying and pasting from other documents I see the warning that there is a hyperlink to another spreadsheet on every time i open the file. While this does no damage, I want my collegues to work with the thing without seeing an error message every time the open the tool.

Now, when i choose data -> edit links (or similiar, I'm working on a german version excel) I see one link to another file, I choose "delete link" and nothing happens. Meaning: The link is still displayed, I still get the warning. I try to make the external source point to the actual file I'm working on, I get an error message that the link does not work.

I want to work on the cell directly that conains the offending link, how can I find it? The spreadsheet is large and contains many sheets. Is there a tool or function that can help me here?

Any other ideas on how to solve this?

2 Answers 2

1
  1. Do a Find.
  2. Search for :\ (assuming your link will point to a mapped drive, eg C:\. If it's pointing to a location on a server try searching for \\ instead)
  3. Set to search within the entire workbook.
  4. Then click Find All.
  5. Scan down the list in the Formula column of the results section for any offending links.
  6. Use the Replace functionality to alter the links as required.

find all links

6
  • I did, the search (for both variants) shows nothing - while 'data' -> 'links' shows a link to 'C:\ ...' what can this be?
    – mart
    Oct 2, 2013 at 14:41
  • 1
    Ah, I see. You must have a hyperlink attached to a cell rather than linked within a formula. Have a read of this and see if running this VBA helps: extendoffice.com/documents/excel/859-excel-list-hyperlinks.html
    – Andi Mohr
    Oct 2, 2013 at 14:46
  • I did, the list generated by the macro is empty (only the column headers, no error messages).
    – mart
    Oct 2, 2013 at 14:49
  • 1
    OK, try looking in your Formula Name Manager (on the Formulas tab). You may have a named reference to an external file. Also check if you have any hidden worksheets.
    – Andi Mohr
    Oct 2, 2013 at 14:55
  • Did both things, no success. By now I'm really puzzled. But with the name mnager I found lots references to external files (that are unreachable) that do not generate the warning that started the whole thing. curios and curioser.
    – mart
    Oct 2, 2013 at 15:17
0

When copying/pasting between Excel Workbooks, Excel copies more than just the cell data. It copies anything and everything associated with the data. This includes Font styles, cell formatting, formulas, data validation rules, conditional formatting rules, hyperlinks, even network location. This "feature" causes mysterious/unexpected behaviors, especially when multiple users are involved.

To answer this question, this web page details various ways to find and remove external links.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .