Good afternoon,
I have developed a dashboard that tracks daily activity for technicians. It's primary purpose is to see what progress is being made throughout the day and this has been working great. However I am now trying to apply some conditional formatting to the results column.
In column "I" I have the total count of calls completed so far for the day. The dashboard refreshes the data from the data base every 30 minutes. What I am attempting to accomplish now is have column "I" high lighted green based on the time of day and the number represented in column "I". For example by 10:00 AM every tech should have completed 1 call. By 11:30, 2 calls, by 1:30, 3 calls and so forth. I have my first conditional rule setup as =AND(NOW()>=TIME(10,0,0),$L$8:$L$20>=1) followed by a formula that reset the cell if cell is not blank. Followed by =AND(NOW()>=TIME(11,30,0),$L$8:$L$20>=2). This continues with a reset formula followed by the next time formula etc...
If I use $L$8:$L$20>=1 nothing highlights. If I use $L$8 and apply it, the cells never reset using rule 2. It should also be mentioned that the total count column is a calculated column using Countif. This may be part of the problem but now sure.
Any suggestions?
I have developed a dashboard that tracks daily activity for technicians. It's primary purpose is to see what progress is being made throughout the day and this has been working great. However I am now trying to apply some conditional formatting to the results column.
In column "I" I have the total count of calls completed so far for the day. The dashboard refreshes the data from the data base every 30 minutes. What I am attempting to accomplish now is have column "I" high lighted green based on the time of day and the number represented in column "I". For example by 10:00 AM every tech should have completed 1 call. By 11:30, 2 calls, by 1:30, 3 calls and so forth. I have my first conditional rule setup as =AND(NOW()>=TIME(10,0,0),$L$8:$L$20>=1) followed by a formula that reset the cell if cell is not blank. Followed by =AND(NOW()>=TIME(11,30,0),$L$8:$L$20>=2). This continues with a reset formula followed by the next time formula etc...
If I use $L$8:$L$20>=1 nothing highlights. If I use $L$8 and apply it, the cells never reset using rule 2. It should also be mentioned that the total count column is a calculated column using Countif. This may be part of the problem but now sure.
Any suggestions?
