Search the Elsmar Cove!
**Search ALL of Elsmar.com** with DuckDuckGo Especially for content not in the forum
Such as files in the Cove "Members" Directory

Track audit findings on Excel tracker

Ajit Basrur

Staff member
Admin
#1
Need help on the attached internal audit tracker. In the Column, "Tracking", I have added the difference between the due date and TODAY. How do I conditional format with 3 colors - one for today (yellow), Less than due date (green) and overdue (as red)?

Also how to create graphs (like a pie) for the total # of yellow, green and red with percentages?

Thank you in advance
 

Attachments

Ajit Basrur

Staff member
Admin
#5
I was wondering if it is possible to include multiple scenarios in the Tracker column (column J)? For example,
  1. Report numbers only if the Status (column I) is "Open"; and just color the cell green with no numbers
  2. If Response Plan Due Date (column H), the Tracking (column J) will look for Due Date for Response (column G).
Thanks in advance
 

Attachments

L.Soad

Involved In Discussions
#6
Hey,

hereafter, new version,
1) see line 17 for example, the formula is applied to the whole column (if you put back «open» in the I17 cell, tracking number of days will reappear)​
2) Column J is already showing H versus the day we are. what do you want to show concerning the «response plan approved on» (G) ?​
You can find help on excel forums with real pros if you want more specialized tasks done by your excel sheet, one of my coworkers is coding in VBA and its amazingly powerful for a free resource in excel.
 

Attachments

Ajit Basrur

Staff member
Admin
#7
So, there are two parts that I forgot to tell you earlier. After the audit report is issued, the owner has to submit a plan like a pre-execution approval (Due Date for Response; column F) and once this form is submitted with action dates, those dates get entered in column H.

So was wondering if Tracker (column J) be able to track the Due Date of Response initially and if that date is entered, will look for a date in the column H to give the result (green, yellow, red) in column J.

Thanks L.Soad, you have been so very helpful

1583941113089.png
 

L.Soad

Involved In Discussions
#8
Okok

So what i've done is:
  • If there is something else than Open in I, put nothing, leave cell green
  • If there is Open in I, and there is a date in H, tracking will compare it to today's date (you can leave the date in F, it wont affect if there is a date in H)
  • If there is Open in I,but there is no date in H, but there is a date in F, tracking will compare it to today's date
  • If there is Open in I, and no date in H or F, cell will remain empty in green
I guess its basically what you asked except that it is not
if Tracker (column J) be able to track the Due Date of Response initially and if that date is entered, will look for a date in the column H to give the result (green, yellow, red) in column J.
But
if Tracker (column J) be able to track the Due Date of Response initially and if that date is entered, will look for a date is entered in the column H to give the result (green, yellow, red) in column J.
tell me if its ok
 

Attachments

Top Bottom