Erratic Conditional Formatting problem

I’m hoping someone might be able to offer some help with a spreadsheet problem.

I’m trying to set up a dynamic weekly planner page in Calc. After several days trial, error and an unhealthy amount of YouTube tutorials, I’ve got everything set up and working except for Conditional Formatting which is behaving so erratically I’m now pretty sure it’s not actually me at fault and and actually a glitch.

I’m hoping someone might have some suggestions for a work around, or can point out if I have in fact missed something blatantly obvious.

So far I have a weekly page set up to display a full week, Monday - Sunday, with formulas to automatically generate a page title with the month, dates for that week and the year, along with daily dates. I also have a mini calendar showing the previous month and the current month.

The formulas are working perfectly and each time I duplicate the sheet, the new sheet shows the following week with all the correct dates as well as changing the monthly calendars as required. My plan being to have 52 sheets, one for each week of the year as a perpetual planner. So far so good.

Then I tried using conditional formatting, which is where I’m running into problems.

I selected the mini calendar cells and set up conditional formatting so only dates in that calendar month were visible, setting each calendar rule separately (previous month and the current month). Works perfectly and is accurate on all copied sheets.

Next I selected both mini calendar cells and set conditional formatting so the dates displayed on that sheet’s week were bold on the mini calendar. Again, this works correctly on all copied sheets without any problems.

Now my problem. The following conditional rules all work on my first weeks sheet and correctly update if I change the reference dates.

I set up a sheet with public holiday dates and then set up a conditional format rule to change the daily date cell background if that day was a public holiday. I set up a matching rule for the current month mini calendar.

I then set up a sheet with waste collection dates and set up a conditional rule for the daily date cell to change colour if there was a collection due, blue if it was recycling or green if it was household waste.

Both of these last two are having problems on following sheets.

All the formulas and the first two conditional format rules copy and work every time I go to the sheet tab and copy a sheet. The Public holiday rules copy, but don’t always work (I had one sheet where the daily date was affected, but the mini calendar wasn’t, one where the mini calendar worked, but the daily date didn’t and one where neither worked.) The waste collection rule copies, but won’t work at all on the copied sheets.

I’ve tried other suggested options. Instead of copying each tab individually, I made the additional 51 weekly tabs, selected the entire current worksheet (ctrl+a) and paste it to the new tabs, I also tried just selecting the cells I’m using and pasting to the new sheets as well as both options using paste special to ensure it included format. All those options gave very odd results in the Conditional format box. It changed cell addresses, altered formulas, added conditions, removed conditions and even added conditions for cells I wasn’t even using.

So far I’ve found the only way that is working is to go to the worksheet tab, right click and copy the sheet, then go back to the previous sheet, select the working area, clone formatting, go to the new sheet and apply the clone. Then everything seems to work perfectly. But trying to do that for 52 sheets is taking forever, I’m assuming there is supposed to be a better way. I’m also concerned that if I don’t find out the cause of the problem, there is always the risk that the issue could reappear further down the line, even after all that effort.

I’m running Libreoffice on Linux Mint

Please upload an ODF type sample file here.

WeeklyAgendaCalendarPage1.ods (28.3 KB)
This is the file I’m working on.

In the example, which conditional formatting doesn’t work? Or which operation (like copying a tab) makes the conditional formatting to fail?
Also, the version you have may be buggy, could you try upgrade to the latest version and see whether it solves your problem.

In my uploaded file, The three highlighted (image attached here) work on every sheet, no mater how I copy it.

The Top entry works more often than not, the second one won’t work at all when I copy a sheet.

Copy from the sheet tab causes the least extra problems. Adding a sheet and then copy/pasting from a different sheet causes the most problems with conditional format rules being altered, added or removed.

The only way I’ve found that so far works correctly is to copy the sheet from the tab, go back to the previous sheet and then use the clone formatting option to clone the format to the new sheet. So far this keeps everything working as expected.

I’ve just updated Libreoffice to the latest version ( and still have the same issue.

I think that I have discovered what is going on. When I duplicate the sheet Week1, the reference to the sheet Public Holiday (the 5th sheet in the list) is transformed to the new 5th sheet of the list which is now Sheet6.
This is definitively a bug.
A temporary workaround is to put the sheets Public Holiday and Bin Collections at the first sheets on the left. Then, making a duplicate seems to work properly.
I am opening a bug at LibreOffice.
Edit: a bug report already exists


Thank you so much, that has been driving me demented for days.
I can confirm that moving the reference sheets to the start fixed the issue instantly.