I copied a table from another source and want to use it in LibreOffice Calc. But I have some dates missing. I need to insert zero values in missing dates to have a calendar view. How to do that?
How many columns do you have? If you just have dates in sheet1 in Column A and your events in Column B, you can create a new sheet2 first and drag the dates to have a full sequence, then do a lookup to populate the events from sheet1
Let’s say your imported dates are in sheet 1.
On sheet2, across a cell range equivalent to that of sheet1, apply the function:
IF(Test,Then_value, Otherwise_value)
Test
is whether the corresponding cell in sheet1 is empty or not. For that you can use the function:
CELL(TYPE, Reference of cell in sheet1)
If the cell in sheet1 is blank, CELL() returns “b”. If so the Then_value should be “0” or whatever you want. If not so the Otherwise_value should be that of the corresponding cell in sheet1.
Hi
You can do this with just Copy & Paste:
- Copy the all range, keep it selected,
- Type 0 and validate with
Alt + Enter
(the entire range contains zeros), -
Edit
Paste special:
checkPaste All
&Skip empty cells
- Confirm
Yes
the warning dialog (You are pasting data into cells that already contain data…)
That’s it…
Regards