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

Elsmar Forum Sponsor

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

Thread starter Similar threads Forum Replies Date
D What are the important things to keep track of in product audit checklist? Software Quality Assurance 3
K Is my Internal Audit Plan on the right track? Internal Auditing 0
M ISO 9001:2000 Internal Audit Procedure - On the right track? Internal Auditing 10
R How to keep track of employee read-only training ISO 13485:2016 - Medical Device Quality Management Systems 13
Ed Panek Requirement to track and trend positive feedback from customers ISO 13485:2016 - Medical Device Quality Management Systems 12
R How to keep track of all FDA rules and regulations for medical device companies 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 2
Sam Lazzara Expedited, fast-track Notified Body Technical File reviews EU Medical Device Regulations 3
S Employee Training Matrix - Keeping track of employee training on various SOPs ISO 13485:2016 - Medical Device Quality Management Systems 4
S Keeping track of the changing Medical Device Standards Other Medical Device Related Standards 3
S Track Cpk trend Statistical Analysis Tools, Techniques and SPC 5
S How to track and manage all uncertainty budgets - Database? Measurement Uncertainty (MU) 3
L Creating a xlsx Customer Complaint file to track Complaints Excel .xls Spreadsheet Templates and Tools 2
F Fast Track Registration in Costa Rica for Medical Devices registered in Canada Canada Medical Device Regulations 3
O Creating a Tool to Track & Verify Mistake Proofing Devices Document Control Systems, Procedures, Forms and Templates 5
Marc Retail stores plan elaborate ways to track you After Work and Weekend Discussion Topics 0
S Does anyone use a FRACAS system to track lab equipment issues? Calibration and Metrology Software and Hardware 1
M Developing a new Plating Vendor - What software to track certifications Software Quality Assurance 2
F Software or form/templates program to track Internal Audits and Corrective Actions ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 9
L "Morale" as an indicator - How to track "morale"? Misc. Quality Assurance and Business Systems Related Topics 16
V What is best practice to track Quality Teams Effectiveness & Efficiency. Quality Manager and Management Related Issues 4
AnaMariaVR2 9 Trends to Track in the Medical Device Industry 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 0
L How to track the NCR (Quality Notification, Services Notification) Nonconformance and Corrective Action 2
G Document Revisions - How do I track the Form Revision History/Approvals? Document Control Systems, Procedures, Forms and Templates 5
N Best Custom Software (To Track QMS & Processes) Provider Quality Assurance and Compliance Software Tools and Solutions 9
A Does anyone know of a WHO fast-track program for Medical Devices? Other Medical Device Regulations World-Wide 3
M Bottom-Up Methodology to Track Root Cause? Problem Solving, Root Cause Fault and Failure Analysis 4
K Looking for Ideas to Track Customer Requests and PPAP Data Packages APQP and PPAP 4
T Deviation Tracker - Anyone have a template that can efficiently track 4 days of data? Customer Complaints 2
N Fast Track for Medical Devices Registration in Mexico that are already CE Marked Other Medical Device Regulations World-Wide 11
L Looking for 5S database to track findings Lean in Manufacturing and Service Industries 6
N Is there a standard length of time to track down non-responding customers w recalls? Misc. Quality Assurance and Business Systems Related Topics 2
M AS9100C - On track to IAQG timeline? AS9100, IAQG 9100, Nadcap and related Aerospace Standards and Requirements 2
T Keeping Track of Procedure Implementation Training Activities ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 6
AnaMariaVR2 FDA's Transparency + Basics for Industry + FDA Track US Food and Drug Administration (FDA) 3
A Corrective Action Tracking - What Measureables and How to Track Status ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 11
A Document Relationship Matrix - Keeping Track of References to Related Documents ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 3
Mr.Happy Dbase or Excel file to keep track on our internal failures (Non conform products) Document Control Systems, Procedures, Forms and Templates 8
Wes Bucey What to think when a major manufacturer goes off the Quality track? US Food and Drug Administration (FDA) 3
L When a tool fails calibration how do you track what product was checked with it? AS9100, IAQG 9100, Nadcap and related Aerospace Standards and Requirements 31
C A form to track Action Items needed attached Document Control Systems, Procedures, Forms and Templates 7
R Documentation Review Matrix to track all documents written by my QA engineers Document Control Systems, Procedures, Forms and Templates 4
A Control Chart to Track Effectiveness of Actions to Reduce Scrap Rate Six Sigma 7
A Analyzing Corrective/Preventive Actions and How to Track Due Dates Preventive Action and Continuous Improvement 16
J Coming up with initial policies sufficient to get everyone headed on the right track ISO 13485:2016 - Medical Device Quality Management Systems 3
G Track bugs, corrections, reports and progress - Test Management Tools suggestions Software Quality Assurance 4
C Archiving Systems to Track Documents - Pharmaceutical Company Document Control Systems, Procedures, Forms and Templates 5
A How to Track Objectives and What do to after objectives had been met? Internal Auditing 7
L Supply Chain World conference and expo question - taking an educational track? Supplier Quality Assurance and other Supplier Issues 3
Ajit Basrur How do you keep track of various passwords ? Coffee Break and Water Cooler Discussions 24
J Access Database to Track Non-Conforming Material Quality Assurance and Compliance Software Tools and Solutions 8
Similar threads


















































Top Bottom