What I need is a formula that will work in column LP for each row.
Info that may help:
Each person has two rows because they can be in 1 of 2 location each day. Some days they may spend a few hours in each location.
My range is from F3 to LO3 for each person. All columns below row 2 are manual entries, except column LP!!
I only need to track consecutive zeros but not include RDO's
The date in the LP column will change to todays date using =TODAY()
Once any row for a person hits 14, meaning 14 consecutive zero's, not including RDO's their name has to change to yellow.
If a person gets any number on any given day, other than zero or RDO the count has to start over. Basically RDO's should be completely ignored, and only zero's get counted
In the example you can see what it should look like and what should happen.
On 6/29 column LL will get a manual input of either a whole number in quarter hour increments or get a zero or get another RDO.
LP3 counted 10 consecutive zero's and on 6/29 if they get a any number other than zero that current value of 10 has to change to a 0. If they get a zero on 6/29 that current value of 10 has to change to 11
LP4 counted 1 zero and on 6/29 it will either change to a 2 or if they get a number it has to change to 0
LP7 has to change to either 0 or 14, depending on what gets entered on 6/29. If it changes to 14 their name has to highlight in yellow.
LP10 has a 14 which means that their name should have highlighted in yellow on 6/28. If they get a number on 6/29, that value of 14 has to change to 0 and their name will lose the highlight.
I have a sheet on GDrive. Any help would be very much appreciated. Thank You.
Info that may help:
Each person has two rows because they can be in 1 of 2 location each day. Some days they may spend a few hours in each location.
My range is from F3 to LO3 for each person. All columns below row 2 are manual entries, except column LP!!
I only need to track consecutive zeros but not include RDO's
The date in the LP column will change to todays date using =TODAY()
Once any row for a person hits 14, meaning 14 consecutive zero's, not including RDO's their name has to change to yellow.
If a person gets any number on any given day, other than zero or RDO the count has to start over. Basically RDO's should be completely ignored, and only zero's get counted
In the example you can see what it should look like and what should happen.
On 6/29 column LL will get a manual input of either a whole number in quarter hour increments or get a zero or get another RDO.
LP3 counted 10 consecutive zero's and on 6/29 if they get a any number other than zero that current value of 10 has to change to a 0. If they get a zero on 6/29 that current value of 10 has to change to 11
LP4 counted 1 zero and on 6/29 it will either change to a 2 or if they get a number it has to change to 0
LP7 has to change to either 0 or 14, depending on what gets entered on 6/29. If it changes to 14 their name has to highlight in yellow.
LP10 has a 14 which means that their name should have highlighted in yellow on 6/28. If they get a number on 6/29, that value of 14 has to change to 0 and their name will lose the highlight.
I have a sheet on GDrive. Any help would be very much appreciated. Thank You.
