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

Ask Your Question

Display and print a blank

asked 2020-08-15 08:50:55 +0200

Yorg gravatar image

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?

Many thanks in advance for any help you can give.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-08-15 09:52:31 +0200

keme gravatar image

updated 2020-08-15 09:53:53 +0200

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

edit flag offensive delete link more

answered 2020-08-17 05:14:13 +0200

Yorg gravatar image

Thanks, that works.

edit flag offensive delete link more



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).

keme gravatar imagekeme ( 2020-08-17 10:44:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-15 08:50:55 +0200

Seen: 96 times

Last updated: Aug 17 '20