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?

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

| improve this answer | |
  • I did, the search (for both variants) shows nothing - while 'data' -> 'links' shows a link to 'C:\ ...' what can this be? – mart Oct 2 '13 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 '13 at 14:46
  • I did, the list generated by the macro is empty (only the column headers, no error messages). – mart Oct 2 '13 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 '13 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 '13 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.