We will be migrating from Ask to Discourse on the first week of August, read the details here

# Display and print a blank

I am contemplating the move from Excel for our business. I'm trying Calc v7 with a workbook created in XL that is central to our operations, so I have opened it in Calc to do a test.

I have a sheet in this workbook where each cell simply equals its corresponding cell in another sheet. For example, cell A1 has a simple = formula, referring to cell A1 in the source sheet. Where there is no entry in the source sheet, the destination sheet shows a 0. I want it to display (and print) as per the source sheet, so I want it to show a blank instead of 0. I only want to do this in this sheet. I can do this in XL by going to the options for the file and unchecking "show a zero where cells have zero value" (or something like that). I've experimented by changing the setting under tools/options/LibreOffice Cal/View/Display, and un-checking "Zero Values" and that suppresses the zeros nicely - but this is a global setting, not just the sheet.

If I use Format Cells/ Number/Format Code, I see "General" is the default code. I've tried both over-typing it and appending it with a ; and this suppresses the zero cells, but it also suppresses the cells where the source sheet does have entries that are not 0 and which should be displayed.

Is there a way of suppressing the display of zero only for those cells which don't have an entry in the source sheet?

edit retag close merge delete

Sort by » oldest newest most voted

=IF(ISBLANK('source sheet'.A1);"";'source sheet'.A1)

more

Thanks, that works.

more

1

Instead of adding an answer (which is meant for when you suggest an alternative solution), click the tick bullet at the answer which solves your issue, so it goes green. That marks the question as solved in the list, so it can be closed.

It also rewards the one who answered by awarding "karma points".

This is not your regular discussion forum where answers follow in conversational sequence, but rather a Q&A service where answers ar sorted according to "score" (based on "Solved" marking, voting or activity).

( 2020-08-17 10:44:34 +0200 )edit