In this project there were two main asks.  First simplify the process of collecting the relevant information to beable to track the Key Performance  Inndicators for a particular process involving the review of marketing materials by various department and multiple divisions to ensure regulatory and legal compliance.

Several macros were used to move the information around the sheet in lockstep with process.  As an example when a case was submitted for review, the case was added to the data page and then moved to a running list of materials underriew.  When the case was returned form each reviewed, the date was entered and once all review complete, another macro finalized the data and moved the case to a achchive tab.  In this way it was easy to see the state of each case and data was available for the dashboard for all cases, no mater what step of the process they were in.

The resulting dashboard was automatically updated as the cases and associated data moved through the process.  The dash board was dynamic with drill down capabilities, such as highlighting one division.  The dashboard showed 6 rolling quarters but found be changed to fiscal year for meaningful year end reports.

The Dashboars is formated two work stand alone or used to feed a quarterly
PowerPoint presentation.  this presentation used to take hour to prepare for, now it takes minutes.