I have a report titled, "Daily Sales Tracker", that has a separare tab for every day of the month. I have created an additional tab titled, "Reps" which pulls info from every daily tab to indicate if a rep worked or not. The info puuled is as follows:
1=Rep Worked
0=Rep Was Off
X or blank=Rep Did Not Report
We need to know when a rep has not worked for 3 consecutive days (0, X, or blank). I am attempting to use conditional formatting to fill those cells that meet that criteria, but have been having trouble doing do. I tried using the following formula:
=NOT(OR(C6=1,D6=1,E6=1))
and applying to to the following area:
=$C$6:$AB$57
but it's not working.
Does anyone have any suggestions on how to get this working?
Thanks!
It's generally a good idea to provide an "after" view so we can see what the end result "should" look like... it's not very clear.
If the intention is to highlight any days where at that point fewer than 3 consecutive days have been worked then in terms of a possible CF rule:
The IFs are just used to ensure only nec. calcs are performed=IF($B6="",0,IF(C6<>1,1,IF(COLUMNS($C6:C6)>=3,(SUM(OFFSET(C6,,,1,-3))<>3)+0,0)))
(If you have a row starting with 1,1 neither would be highlighted as we don't know the run at the end of the prior month)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks