I need some help with collecting data for trending. Currently, I have multiple projects with all of their top line data dynamically linked to a Roll-up Sheet. On this roll-up sheet I have data that is calculated from the project Status. Ex: How many projects are Red, Yellow and Green. This works really well for live data.
But, management is wanting to collect this data over time. Example: On the first of each month, they want to know the number of projects that are Red, Yellow, Green and create a dashboard showing how the projects are trending from month to month.
I'd like to know how to automatically capture this data on the triggered date (1st of each month). Once I have the data, I could then create a dashboard. I could do this on the current Roll-up sheet or create a separate trend collection sheet if needed.
Please let me know if you have some direction and help.
Rick