Copying column to another tab - empty cells are not empty

Hi, how to avoid copying empty cells when mass copying the whole column from another?

So this is the original tab:
Some days I don’t track my sleep so I leave the cells empty.

The colouring is done automatically with cell formatting based on the hour value.
This is what the total sleep formula looks like for reference, I don’t think it matters:
=IF(V21-U21=0 ,"",(IF((U21>=V21)=1,(1-U21)+V21,V21-U21)+IF((W21>X21)=1,(1-W21)+X21,X21-W21)))

=== MY PROBLEM: ===

In 2nd spreadsheet tab I use formula : $‘tab-name’$U1
so tab and cell using $ and I dragged that across.

But its copying EMPTY cells as 0 time value. These are DATE formatted cells.

I want empty value to be empty white cell visually, not blue…

Here is my conditional formatting by the way I tried to make it white using that but unable to overwrite?

  • I created formatting so if value is 00:00 hours its white text on white background.
    – I applied this to both original tab and the tab that copies it live

These are the other formatting I use:

So I apply a background colour using style based on value of the time of day.

I used “0<=Hour” in the first one, so I log time sleep time AFTER midnight it turns blue. In LibreCalc 24:00 time is the same as 0. Or 00:00. Later I have the same colour applied from 8pm to “hour<24”.

Still not working. The cells in the2nd TAB that copies cells live still shows 00:00 hours in blue for empty cells…

I tried changing my conditional formating from “0<=HOUR(U5)” to absolute value of a cell that I set as 00:01 which means 1 minute past midnight , like : " $‘1’.$U$4<HOUR(U5) " ( and U4 is 00:01 set as time)
still not working, unable to overwrite to make 00:00 hours invisible in the second tab.

Any ideas? Let me know if not clear, I can make a video and link it on youtube or reword this. Many thanks in advance, I appreciate your help :slight_smile:

Version: / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 16; OS: Linux 6.5; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.4
Calc: threaded

it’s probably very clear,
but certainly a bit too verbose for the focus span of the numerous calc gurus here.

I suspect if you attach an actual .ods with minimal sample of your problem, you’ll have more attention :wink:

1 Like

hahaha, Should I close this and make a new threat or just replying will bump it to the new of the top topics ?
Thanks by the way

no, but in the latest yes.

hope not :rofl: