mardi 25 mai 2021

How can I simplify my conditional formatting

My spreadsheet has over 30 conditional formatting rules and I was wondering if I could combine them.

enter image description here

I've attached the picture to give an idea but basically, the user can set/change the cancelation policies on the right (BC9:BF18) by setting a start/end date and selecting a policy.

I then use conditional formatting to color the calendar using this info (each cell of the calendar is set to its actual date, so the blue June 1 cell actually contains 01/June/2021 but is formatted to just show 1). For each row of the BC9:BF18 table, I have the following rules (eg. for first row [9]):

=AND(E9>=$BC$9,E9<=$BE$9,$BF$9="High") - Change to blue
=AND(E9>=$BC$9,E9<=$BE$9,$BF$9="Standard") - Change to white
=AND(E9>=$BC$9,E9<=$BE$9,$BF$9="Peak") - Change to red
=AND(E9>=$BC$9,E9<=$BE$9,$BF$9) - Change to a set color

That's 4 rules for each of the 10 lines (BC9:BC18), so 40 different conditional formats.

I'd now like to add to that that if the day is a holiday (holiday dates will be in a table below, let's say BC30:BC60) then make the font red but keep the background color in line with the cancelation policy.

This will require loads and loads more conditional formatting rules which would need to go above all the old rules eg. =AND(E9>=$BC$9,E9<=$BE$9,$BF$9="High",[OR(E9=$BC$30, E9=$BC$31....)] - Change to blue with red font, Can anyone see a way to simplify this?

I have a few suggestions but I'm not sure if they'll work:

(a) Changing the order so that the end date doesn't matter, the next formula will overwrite on its start date

(b) Checking all policies that match 'High' (so if(BF9:BF18='High'... somehow get that start/end date etc.

(c) for the new bit (checking if the date is in table BC30:BC60) there must be an easier way than checking if the date matches BC30 then BC31 then BC32 `=OR(E9=$BC$30,E9=$BC$31,E9=$BC$32, etc.

Aucun commentaire:

Enregistrer un commentaire