Good morning,

I have spent some considerable time creating a shift planner, which contains 40+ sheets based on months.

The shift pattern I have used has now changed! This has resulted in formula and conditional formatting issues on every sheet. Instead of having to go through every sheet I wondered if someone could help with some code.

What I need is for each sheet G9 - AK9 to change teh formula from:

=SUMPRODUCT(--($A$16:$A$51="NBO")+($A$16:$A$51="NPO"),--(G16:G51="2pm - 11pm")+(G16:G51="3pm - 1am"))

To: =SUMPRODUCT(--($A$16:$A$51="NBO")+($A$16:$A$51="NPO"),--(G16:G51="2pm - 11pm")+(G16:G51="3pm - 1am")+(G16:G51="3pm - 11pm"))

The conditional formatting then needs to change within several ranges: G24 - AK31 & G33 - AK51. Rule two is currently:

=OR(G17="2pm - 11pm",G17="3pm - 1am")

Which needs to change to:

=OR(G17="2pm - 11pm",G17="3pm - 1am",G17="3pm - 11pm")

I need to then replicate this on every sheet in the workbook! Nightmare! I've only been able to include 1 month due to the restrictions on uploads.

Any help would be much appreciated. Example worksheet attached.ShiftIssue_CF_Formula.xls