Dear all,
I’m trying to get my spreadsheets to show Dependencies between cells. But although I can get a blue trace arrow with a blue dot or arrow-head to show that a cell has some precedents or dependents by using Tools>Detective, I can’t click on the arrow as the Help menu indicates that I should be able to to show me exactly which are the dependents/precedent cells, as I can in Excel.
According to the Help for Libreoffice Calc, my mouse cursor should change shape when I hover over a trace arrow, allowing me to double-click to get it to show me the dependencies; but it doesn’t change shape. It does change to a ‘paint-pot’ shape when I use the ‘Fill Mode’ option within Detective, but I don’t think this is what I need.
I have the same problem on my new Raspberry Pi 500 and on an older Lenovo laptop on which I have installed Linux Mint 21. Looking at various sources, I have seen a suggestion that my mouse may be wrongly configured, which would be odd as the same problem appears with the laptop trackpad, the proprietary Raspberry wired mouse I bought with the Pi, and a generic wireless mouse that I have connected to try.
Any suggestions gratefully received.
Many thanks in advance!
Paul.
Please include the following information with all inquiries: operating system, LibreOffice version (four digits, e.g., 25.2.5.1), file type in which the file is saved. Thank you.
Thanks very much for the clarification.
LibreOffice version I’m using is 7.4.7.2
File is saved as .ods Open Document spreadsheet
Operating system is Linux Mint 22.1 Cinnamon (Cinnamon version 6.4.8) on my laptop; I’m afraid I’m not absolutely certain which operating system is running on my Raspberry Pi, but it’s a newish machine (December 2025) whose OS was pre-installed, and I think it’s running Raspberry Pi’s own OS, which I gather is based on Debian Linux ‘Bookworm’.
Best wishes,
Paul.
…I’ve just realised from looking at the LibreOffice website that they’re advertising version 25.8! Not sure why I’ve only got version 7.4.7.2… The suite (Base, Calc etc) was also pre-installed on my raspberry Pi, I wonder if Raspberries don’t cope well with later versions…? Or would it be worth trying to download 25.8 and see if it helps, do you think?
I’ve checked my old Lenovo laptop’s software Manager, and have just remembered that when I do this, it says ‘generating cache, one moment’ and freezes on a spinning wheel, then I get a rather sinister-sounding email about my APT configuration being corrupt and advising me to switch to another mirror, but when I do this nothing happens.
In case it helps: the following License Information appears from the Help menu in my Calc document on the Raspberry:
Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.12; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Debian package version: 4:7.4.7-1+deb12u10
Calc: threaded
However, the following (different) appears in the same place when I’m on my laptop (Linux Mint) working with the .ods document on it.
Version: 24.2.7.2 (X86_64) / LibreOffice Community
Build ID: 420(Build:2)
CPU threads: 4; OS: Linux 6.8; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Ubuntu package version: 4:24.2.7-0ubuntu0.24.04.4
Calc: threaded
Thanks again!
Paul.
Does the formula reference cells in the same sheet or in a different sheet? The implementation for the latter case is weaker than in Excel. You cannot double-click the arrow in that case and the tool “Mark Precedents” brings you only to one cell. BTW, the tool “Mark Precedents” is not automatically included in a toolbar, you have to add it manually.
Thank you very much for your message, @Regina , it’s extremely helpful! My formula references cells in a different sheet, so your answer explains things perfectly: when I create a reference between cells on the same sheet, the mouse pointer does indeed change shape when I hover over the trace arrow, so it seems I have a solution!
I can get the ‘Trace Precedents’, ‘Trace Dependents’ commands etc via the ‘Tools’ and ‘Detective’ menus without having to add the tools to a toolbar, but perhaps I’ve not understood you properly here.
Anyway, thanks so much for taking the time to answer my query, which I suspect might not have managed without your help and that of this amazing community!
The Trace commands aren’t essential to what I want to achieve with my spreadsheet, so for now I’ll persist with using it. In case it’s of interest, my Microsoft 365 subscription expired, and for some time I’ve been experimenting with alternatives. I have a Macbook also, so I thought about exploring Apple’s ‘Writer’, ‘Numbers’ suite but was put off by the reviews in their App Store. I’ve also used Google Docs, Sheets etc. When I tried using Open Office many years ago I was put off by various features that felt unfamiliar from my use of MS Office.
Lastly, I hope you don’t mind me asking: do you think it would be worth updating my version of LibreOffice by downloading version 25.8 from their website? I would have thought updates would have been offered to me automatically when opening my 7.4.7.2 version, but perhaps not.
Grateful best wishes,
Paul
The tool “Mark Precedents” works this way: Set the cell cursor to a cell which has a formula. (For an array formula use the top-left cell). Click on “Mark Precedents”. Now all cells that are used in the formula are marked. It does not draw any arrow and it is not persistent, but only a temporal mark. Similar works “Mark Dependents”. You could add it to the Tools>Detective sub-menu and test it.
That’s brilliant, Villeroy and Regina, thank you both so much!
I’ve installed the Show References extension on my LibreOffice 24.2.7.2 on my laptop running Linux Mint 22.1 Cinnamon (6.4.8 - see above), and it works great! Villeroy, have you written the code for this yourself?? Can something similar be done for ‘Show Dependents’?
However, because my Raspberry appears to be running LO 7.4.7.2 (see above), when I try to install your extension on it I get an error message telling me it requires LO 24 or above.
I’m tempted to go ahead and download the latest version of LO from their website, but I’m puzzled that when I go to the ‘Recommended Software’ list under ‘Preferences’ from the Raspberry’s Start key, I don’t see an option to update version 7.4.7.2, although I can see LO listed under ‘Office’ with the other pre-installed apps.
Should I perhaps use ‘Add/Remove Software’ to remove LO and then hope that if I then Add it again a more recent version will be added? Or should I download LO from their website instead and hope it overwrites or updates the version I have installed? Or is the reason the Raspberry came with only LO 7 installed, because later versions don’t run so well on the Raspberry OS?
If I should download it from libreoffice.org, what operating system should I select? I’ve not found it easy to establish exactly which operating system I’m running. I’m guessing it’s Linux (64 bit) (deb) as I gather the Raspberry OS is based on Linux Debian, but how do I tell whether it isn’t, for example, Linux (Aarch64) (deb)?
Perhaps I should address these questions to a specifically Raspberry forum. Sorry to ask so many!
Many thanks again,
Paul.
Yes. With a little help from @Lupp, I learned how to parse formulas with the LibreOffice API.
I don’t know how. I do not see any API for that.
Amazing, thanks a million!! I’m now combing through some Raspberry forums looking for clues. Some intriguing discussions about enabling Bookworm backports instead of building LibreOffice 24 from source, but I have the usual sense that I am several miles out of my depth…
Best wishes,
Paul.
How does the tool investigate the dependencies?
(How do cells/ranges know what to mark “dirty” when they were recalculated?)
I don’t know. The implementation seems to be in
https://opengrok.libreoffice.org/xref/core/sc/inc/detfunc.hxx
https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/detfunc.cxx
At a first glance its
void ScDetectiveFunc::GetAllSuccs(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2, std::vector<ScTokenRef>& rRefTokens)
looks like a “brute force” procedure.
Eike Rathke (erAck) can surely explain how all these detective tools work. Perhaps ask him?
For the detective arrows, the API is XSheetAuditing, I don’t know whether there exist a similar API for marking instead of arrows.
Thanks a lot. And sorry. I may have wasted your time.
In fact I rarely use the detective tools, but I wondered how Calc manages in an efficient way the need to flag dependent cells “dirty” after any recalculation of a precedent.
However, if I’m pointed to the code I wouldn’t understand it.
I’m too old and thinking too slowly. So I’ll let Eike enjoy his retirement.