How to find cells containing file link references in Calc

I have a large spreadsheet where I apparently and not knowingly managed to insert file link references for a number of cells (the cells present values that are fetched from cells in an external Calc file).

I identified this file link referencing when a formula was malfunctioning and I got division by zero. After some research I identified the cell that caused the problem and found out that the problem was due to this external file referencing.

Same thing happened a couple of more times and I once again needed to search for the cell with the file reference that caused this problem.

My question - is there any way in LibreOffice Calc to highlight all cells that contain external references (if so, then I could systematically replace external references with proper internal ones as I have no need for external references)?

Many thanks in advance for any guidance on this topic.

How the links are done? with formulas?

Hi, yes the file references are included in formulas.

Use the Menu/Edit/Find&Replace tool to search for the cells containing file:///, click on Find all, and all the cells with it are selected.

1 Like

Thank you!

This solved the problem.

DOH!! I had the same question, but as soon as I saw your answer, I realized how simple it was. Often, simplicity is genius.

Thank you

Brilliant, this worked for me, too! Thanks!

Or search for :// in case you may also have links to network files like smb://server/share/file

Not working in libreoffice Version: 6.1.4.2 (Build ID: 1:6.1.4-1) and 5.0.4 …
Tried with different search patterns, selected advanced search options, … any clue ?

Found … Libreoffice reported external file links but the error was related to a formula referring a deleted cell (Depenses.E#REF !) with no error on the column result … but reporting a link to an external file “file:///home/username/Depenses.E” …

1 Like

Not working in libreoffice Version: 6.1.4.2 (Build ID: 1:6.1.4-1) and 5.0.4 …
Tried with different search patterns, selected advanced search options, … any clue ?

Found … Libreoffice reported external file links but the error was related to a formula referring a deleted cell (Depenses.E#REF !) with no error on the column result … but reporting a link to an external file “file:///home/username/Depenses.E” …