Hello, i have a minor problem while working on LibreOffice Calc. I will explain the whole thing so you can understand me (English is not my mother tongue).
On the first sheet i am writing the Invoice, and on the second shit is another document. Everything is okay when i have invoice with one article, i type the formula “=$sheet1.d15” for example, and on the second sheet above that i type the next column/row, but if in the invoice is empty i get 0?
So my question is can i write on the second sheet for example “=$sheet1.d15”, then above “=$sheet1.d16” et cetera but if on sheet1 D16 is empty to avoid getting 0 on sheet2?
Thanks in advance!
Heading of this question is misleading. The issue is about not showing zero values as formula results, no matter you put it in the same sheet or in another.
Hi Pan43, the easy way to solve your question is change the format to not show zeroes.
For the document,
Menu/Tools/Options/LibreOffice calc/View - Display - Zero Values,
remove the check.
For cells or styles:
Formatting the cell (Ctrl+1),
modify a style (F11, right click the style, modify),
Select Number tab:
#.##0,00;[RED]-#.##0,00;#
positive;negative;zero
Clicking help while in the box format, you can find useful information about it.
Another option is to write a formula that returns an empty string when the cell in the first sheet is empty.
Instead of the formula
=$sheet1.d15
You could use
=IF($sheet1.d15=0,"", $sheet1.d15)
The If function performs a logical test. When the result of the test is true one value is returned. When the result is false a different value is returned.
In the above formula the test is $sheet1.d15=0. When this test is true you dont want 0 to be displayed on the second sheet so in stead you return an empty string using “”. When the test is false you want the value to be displayed in sheet two so you return $sheet1.d15.