Reporting changes in timelines.
Hello.. I have multiple Gantt sheets to track several project milestones, and client payments associated with those milestones. My finance team wants a monthly report on changes to those milestone payments caused by delays in the project in order to forecast revenue.
My idea is to set up a workflow that will run on the last day of every month. It will move all of the rows that have had changes made to them to a new sheet. This would allow me to keep a running total of those sheets See the example below. There are data from two different times (see modified column - varies by 2 minutes, not a month for this example). Highlighted yellow is the first timepoint, Highlighted blue is the 2nd timepoint and the red finish dates have changed.
I need to report the delay for milestone payments. For example - in this example Milestone 2 is delayed from 11/30/22 to 2/1/23. This would need to be specifically flagged. I will need to repeat this next month.
I tried using the baseline variance function, which showed the delays, but it would not allow a comparison from month to month to highlight those changes. I need to be able to do this across multiple sheets and hopefully roll this into a single report. I hope this makes sense.
Best Answer
-
I published a sample sheet recently that performs this function. Basically you are asking Smartsheet: "Look at every record that (1) matches this PRIMARY COLUMN and which (2) has a MODIFED less than this MODIFIED and give me the MAX record." From there you use INDEX and MATCH to calculate the deltas.
Take a look:
In this example, the formula in the Final Answer field is where you want to end up, but instead of an IF statement, you will just calculate your difference.
If you publish a sheet with your data, and give me the link, I can build it up for you.
Answers
-
I published a sample sheet recently that performs this function. Basically you are asking Smartsheet: "Look at every record that (1) matches this PRIMARY COLUMN and which (2) has a MODIFED less than this MODIFIED and give me the MAX record." From there you use INDEX and MATCH to calculate the deltas.
Take a look:
In this example, the formula in the Final Answer field is where you want to end up, but instead of an IF statement, you will just calculate your difference.
If you publish a sheet with your data, and give me the link, I can build it up for you.
-
Thank you James, I did not quite follow all of the formulas in your sheet, but this looks like exactly what I want.
Here is a link to the sheet I set up up as an example:
Thank you so much for your help,
Tim
-
James - I was able to take what you had published and apply it to my sheet - THANK YOU so much, it works perfectly!
-
Excellent!
Help Article Resources
Categories
Check out the Formula Handbook template!
