I have a sheet called [Calendar] with a calendar for each month of the year, this sheet has conditional formatting to colour each date depending on holidays/capacity for that month. These dates are also set in the [Calendar] sheet.
I have a second sheet for each month (eg. [Jan 2022], this sheet has lots of information about what's happening that month.
I'd like to have that months calendar from [Calendar] show up on [Jan 2022] keeping all of the conditional formatting. Is this possible?
(I realise that I could re-do all of the conditional formatting on the Monthly sheets based on the info in [Calendar] using Calendar!Range in the conditions but there are 45 different sets of conditional formatting as well as a script which changes the border colour of certain cells if they match other conditions on the sheet. I was wondering if there was an easier way)
Ok, After a LOT of messing about, I've found a few possible solutions that worked.
Firstly, you can copy/paste the area you'd like to 'show' which will also copy the formatting over, sadly this formatting doesn't link to the correct page anymore (if, as in my case, you're not also copying the information that the conditions rely on).
The easiest way I found was to copy the fields that the conditions rely on into the same fields on the sheet you're copying to (eg. in the cell Jan!X10 put the code =Calendar!X10 and do the same for X10:Z20 for example). This means that when [Calendar] is updated, the conditional formatting on [Jan] changes to represent this.
[one last tip, after putting the code in the matching cells, you can then either hide these cells OR copy/paste these cells to somewhere that works better in your sheet and the references in the conditional formatting will update]
The other way I found to do it is to change the [Jan] conditional formatting to reference the [Calendar] page, sadly, you cant simply change =A1>X10 to =A1>Calendar!X10, you need to change it to =A1>INDIRECT("'Calendar'!X10") as you cant directly use a cross-sheet reference in Conditional Formatting (for some reason).
I hope this helps anyone who is having the same problems I was having. Happy coding :)