Help with calculated columns / data manipulation to display in dashboards
It's quite a long one, so bear with me!
We have a process where Program Managers provide a status update (among other things) on their programs every 2 weeks (On Track, At Risk, Severe Risk etc.) A program could be sitting at "At Risk" for a 12 months, and this is what I want to display on my dashboard. I want to know what the previous status was as this can show me whether we are trending the right way to On Track and how long it has been "At Risk".
For background, each program has an update sheet which program managers complete every 2 weeks, once submitted this data updates the program dashboard and auto copies the data onto an archive sheet, new rows are added at the bottom of this archive sheet. So I have all the data stored I just need help manipulating and displaying it. Some program managers provide an update more or less frequently, so 2 weeks isn't a hard and fast rule.
Below is what I have at the moment, this report gives me the status from all the updates which is great but it's too much scrolling & data, I want a clean and simple view. In this example you can see the status changed from On Track to At Risk on 24th March. So what I want to display is the Current Status = "At Risk", Status Changed = 24/03/23 or 110 days ago, Previous Status = "On Track", in a nice neat grid.
I'm thinking the best way is to have some calculated columns in my archive sheet to identify the last status change row and then reference the date of that to work out the time difference. But no idea where to start with the calculation to identify the last status change row.
Open to any idea anyone has... Thank you!
Help Article Resources
Check out the Formula Handbook template!