Sep 25 2022 07:00 PM - edited Sep 25 2022 07:10 PM
Hi members - I was wondering if someone could help me with what should be an easy formula. I need a formula in conditional formatting to highlight in red if there is more than 6 consecutive cells in a row with the same text "Regulated Work" - this is required to highlight if someone was tasked to work more than 6 days straight in a roster.
Thanks
Sep 25 2022 09:17 PM
Solution@Muffinman Perhaps the example in the attached workbook does what have in mind.
Sep 26 2022 01:16 AM
Sep 26 2022 03:23 AM
This is just playing.
WorksheetFormula = LET(
consecDays, SCAN(0, roster="Regulated Work", Consecλ),
excessDays, consecDays>6,
FILTER(day, excessDays));
Consecλ
= LAMBDA(a, b, IF(b, a+1, 0));
returns a list of excess days scheduled. The array excessDays could be used for conditional formatting if the CF functionality were not so antiquated (I suspect the code is out of living memory). If you simply wanted to conditionally format the name or the entire row, the following formula could also be used
= OR( SCAN(0, roster="Regulated Work", Consecλ) > 6 )
I realise that this probably looks totally over the top but, for me, almost every formula in the workbook is a Lambda function so anything else looks kind of out of place.
Sep 26 2022 04:14 PM
Sep 26 2022 04:22 PM
Sep 25 2022 09:17 PM
Solution@Muffinman Perhaps the example in the attached workbook does what have in mind.