Conditional formatting and rolling totals

**Big_Kev007** · 01-21-2022, 12:37 PM

He Everyone

I was hoping to get some help on conditional formatting based on the total running values of previous cells.

At the moment I am trying to create a scheduling spreadsheet that will highlight (conditional formatting) when any person has worked more than 13 consecutive workdays.

So if the person worked a 5 day week for the past 3 weeks when a value other than 0 was present in the 4th cell (4th week) it would highlight this.

I have attached a small example of what I am looking for (colour added manually for now) and hoping beyond hope someone can help me.

Many thanks in advance for any help I receive.

**Richard Buttrey** · 01-21-2022, 12:52 PM

Hi,

I note you are using merged cells. Most of us avoid these like the plague (or covid) since they cause too many problems.
A solution to this is complicated by the fact that your values have two empty cell between them

Are you able to capture data with dates in successive columns?

**Big_Kev007** · 01-24-2022, 05:05 AM

Hi Richard

I am currently using a spreadsheet that I inherited but can can create another or another sheet within the workbook if needed.

So short answer long, yes I can create a sheet with consecutive date columns if that helps.

Based on the above what would you suggest as a solution?

Thanks

**JeteMc** · 01-24-2022, 09:40 PM

I feel that Richard is talking about something similar to what is in the attached file.
The formula used as a conditional formatting rule in A9:H11 is: =AND(SUM(A9:C9)>=13,D9>0)
Note that this formats cells D9:H9 as the sum of the previous three weeks is 15 and none of those cells has a value of zero.
Let us know if you have any questions.

**Big_Kev007** · 01-27-2022, 05:20 AM

JeteMc

Worthy of the Guru title. Thank you so much for this it will fit my needs perfectly.

Once again the forum has shone.

Thx

**Richard Buttrey** · 01-27-2022, 08:01 AM

Originally Posted by JeteMc

I feel that Richard is talking about something similar to what is in the attached file.
The formula used as a conditional formatting rule in A9:H11 is: =AND(SUM(A9:C9)>=13,D9>0)
Note that this formats cells D9:H9 as the sum of the previous three weeks is 15 and none of those cells has a value of zero.
Let us know if you have any questions.

@ JeteMc
Indeed I was. Well done

**JeteMc** · 01-27-2022, 09:55 AM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Conditional formatting and rolling totals

LinkBack

Thread Tools

Rate This Thread

Display

Conditional formatting and rolling totals

Re: Conditional formatting and rolling totals

Re: Conditional formatting and rolling totals

Re: Conditional formatting and rolling totals

Re: Conditional formatting and rolling totals

Re: Conditional formatting and rolling totals

Re: Conditional formatting and rolling totals

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] conditional formatting extends to TOTALS row

Conditional formatting for rolling date period

Conditional Formatting for the Grand Totals.

Verifying Totals with Conditional Formatting

Running totals with conditional formatting

[SOLVED] Conditional formatting for rolling 12 months

[SOLVED] conditional formatting with totals

Bookmarks

Bookmarks

Posting Permissions