In Calc, does it have a way to display a zero in a cell if the cell is empty?
So you want the entire spreadsheet to be filled with zeroes initially instead of empty? Try to think of an example of how this would work, or when it might be useful.
No no, just a few ones! Not a whole sheet…
The cell may have any content/non-error result and you can use/misuse the ‘Numbers’ format codes to display whatever you want. It may be any kind of lie, including “I’m blank”.
You cannot display any kind of string as long as the cell is empty.
But if there anyway is need to distinguish the cells showing 0 from the blank cells displaying nothing: Why not enter the 0 there? Simple and even truthful!
Cell with 0s will show users of the spreadsheets where they have to enter data and if the cell is at 0 it will indicate something is very likely missing
To inform usrs of where input is expected I would by far prefer a conditional format overlaying a cell style with an alerting background color.
In addition to the last wise Lupp’s comment, it’s possible allow the access to only those cells with ‘cell protection’ disable when ‘sheet protection’ is enable.
Regarding the comment by @bern (Original Questioner) on the Original Question:
To show users where input is expected I would by far prefer a conditional format overlaying a cell style with an alerting background color. The Formula to use in the ‘Formula is’ mode of CF might be ISBLANK(B5) then, if B5 is the leading cell of the range to define the CF for.
See this demo.
In addition: If only cells expecting input are set to ‘not protected’, and the sheet itself is protected, ‘Tab’ will jump to the next input cell.
Thanks Lupp, I’ll go your way as I don’t have much choices anyway.
There is not. Empty is empty.
Hello @bern,
You could use a macro for this.
For example to set the value of cell A1 to zero if cell A1 is empty, you could call Cell_Blank_To_Zero( "A1" ):
Function Cell_Blank_To_Zero( strCellAddress$ )
Dim oSheet As Object : oSheet = ThisComponent.CurrentController.ActiveSheet
Dim oCell As Object : oCell = oSheet.getCellRangeByName( strCellAddress )
If oCell.String = "" Then oCell.Value = 0
End Function
HTH, lib
It is close. It enters a 0 if the cell is empty when I setup the function in a spreadsheet and the cell is empty. If I enter something in the cell then delete it the cell stays empty, 0 is not coming back.
Thanks for your suggestion
Cells A1:A30 have numbers in some of its cells. Cell B1 has the following formula:
=IF(A1>0;A1;0)
Fill down from B1 to B30.
Obviously change the cell references to your needs. Hope this helps.
OUCH! Sorry!