I am having a problem with calculations using one spreadsheet trying to reference a different sheet.
I am trying to migrate from openOffice to LibreOffice. A spreadsheet that works in openOffice fails in Libre.
I have attached a simple spreadsheet that is an example.
Test.ods (105.7 KB)
Hi Roger, and welcome!
I think you have not quite correctly identified the source of the error - it is not that the data is on another sheet, but that there is no data there. I mean that the source of the error is empty, unfilled cells in the Credit and Debit columns. Calc is very strict about data and tries to warn the user about missing data using errors like #VALUE! instead of treating an empty cell as zero.
You can correct the calculation in several ways.
For example, you can enter zeros where they should be (this is long and boring, although several macros have been published here that do this).
You could also change the formula to something like =SUMPRODUCT(month=$I$2;account=$B4;N(credit)-N(debit)) and get your $485.00.
You can also change the description of the named range by inserting the N() function directly there.
It seems that changing this parameter also solves the problem, but I do not welcome such changes (in the case of switching to an office on another computer, it is quite difficult to immediately remember what exactly needs to be changed so that your table starts working)
The formula in $Calc.$D$4 Lacs
=SUMPRODUCT(month=$I$2;account=$B4;credit-debit)
It is likely that the default value of the “Conversion from text to number” option in OpenOffice is different from the current default value of this option in LibreOffice.
Hello,
I want to thank everyone that has responded even though I failed to provide the important information like OS, and Libre version. Responder JohnSUN solved my problem by putting the N function around debit and credit.
I am on my way to happiness.
Thanks again to everyone for your responses.
