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 7.3.7.2 on Linux Mint
