Data Tracker and Dashboard Design

I need a excel spreadsheet for tracking the progress of corrective actions. Each corrective action will include a number of tasks with assigned due dates for completion. All tasks must be completed in order to move the corrective action to the closed status. I also want the spreadsheet to include the date that the corrective action was created, the tasks owner, the due date for the task, root cause, progress of the task to date (choices will be from a dropdown list; no update, in progress, closed task), and whether the task is overdue based on the proposed completion date. Lastly, I want this information to feed into a CAPA Dashboard that summarizes the information. **Corrective Actions worksheet (please note that all Corrective Action Plans consist of multiple tasks. I want to track the over CAPA and the associated tasks) * CAPA ID * CAPA Title * Date Created * Root Cause * CAPA Owner * Task Description * Task ID * Task Owner * Task Due Date * Task Status** (drop-down) - No Update - In Progress - Closed Task * CAPA Progress % (tasks completed/task assigned) * Task Completion Date * **Overdue?** (automatically flags overdue tasks) * CAPA Status (drop-down) - Open - Investigation/Action Plan - Implementation - Effectiveness Review (= all tasks completed) - Closed I would also like to include: * Drop-down lists for task status * Automatic overdue calculation based on due date and task status * Conditional formatting to highlight overdue items * Excel Table for filtering and sorting The above will then feed into a Dashboard worksheet that summarizes: * Total Tasks * Open Tasks * Closed Tasks * Overdue Tasks * Total CAPAs | | Average Days Open | Mean age | | Average Time to Closure | Closed CAPAs | | CAPAs by Root Cause | Pareto chart | | Tasks Due Next 30 Days | Upcoming work | | Effectiveness Checks Pending | Open effectiveness verifications | --- The workbook can automatically close a CAPA when: * Every task = Closed * Effectiveness Check = Complete * Closure Date entered Otherwise it remains Open. For the Dashboard, I would like some visuals With charts such as: * CAPAs by Month * Root Cause Pareto * Tasks by Owner * Overdue Tasks * CAPA Aging (0–30, 31–60, 61–90, >90 days) * Progress Gauge (% Complete) * Open vs Closed Trend -

Project ID: 40553635