Track audit findings on Excel tracker

Ajit Basrur

Leader
Admin
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

  • Audit Finding Tracker (Internal) Elsmar.xlsx
    22.3 KB · Views: 663

L.Soad

Involved In Discussions
Hey Ajit,

I've added 3 conditional formats, you can change these rules with the last button on the conditional formatting tab (sorry i dont have excel in english, so i dont know the name, but it should be «manage the rules»)

I'll be looking for your piechart this afternoon
 

Attachments

  • Audit Finding Tracker (Internal) Elsmar.xlsx
    23 KB · Views: 333

L.Soad

Involved In Discussions
allright, i've done a really basic graph, see if it suits you. It should be updating automatically if you add some audit findings

You'll need to adapt it acording to your needs ;)
 

Attachments

  • Audit Finding Tracker (Internal) Elsmar.xlsx
    28.3 KB · Views: 386

Ajit Basrur

Leader
Admin
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

  • 1583933680993.png
    1583933680993.png
    8.2 KB · Views: 323

L.Soad

Involved In Discussions
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

  • Audit Finding Tracker (Internal) Elsmar (3).xlsx
    28.4 KB · Views: 287

Ajit Basrur

Leader
Admin
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
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

  • Audit Finding Tracker (Internal) Elsmar (3).xlsx
    28.5 KB · Views: 729
Top Bottom