I’m looking to add a couple of columns into my project plan sheets. SmartSheet already has a [Start] & a [Finish] column built into its project template, but I’m looking to differentiate between [Plan Start] & [Actual Start] and [Plan Finish] & [Actual Finish]. I renamed [Start] & [Finish] to [Plan Start] & [Plan Finish].
I currently have a column formula in the [Projected] column that, along with a few helper columns added into my sheet, looks for the latest row where [Actual Finish]<>”” and then adds the sum value of all cells in the [Duration] column that occur below that latest row to that [Actual Finish] date. The output is a projected completion date for all subsequent rows as well as for the project overall.
My formula thus far:
=IFERROR(IF([Row Number]@row - [Max AF Row Number]@row < 0, "", IF([Parent-Child Test]@row <> 0, "", IF([Actual Finish]@row <> "", [Actual Finish]@row , WORKDAY(MAX(COLLECT([Actual Finish]:[Actual Finish], [Actual Finish]:[Actual Finish], @cell <> "", [Parent-Child Test]:[Parent-Child Test], @cell = 0, [Row Number]:[Row Number], @cell < [Row Number]@row )), SUMIFS([Days]:[Days], [Row Number]:[Row Number], @cell <= [Row Number]@row , [Actual Finish]:[Actual Finish], @cell = "", [Row Number]:[Row Number], @cell >= [Max AF Row Number]@row ))))), "")
Here are the columns used in the formula:
- [Actual Finish] is a user-entered column that marks the date that the work for a given row was completed
- [Days] is a hidden helper column that mimics the value of that row's stock [Duration] column, but removes the “d” to return only [Duration]'s numeric value
- [Row Number] is a hidden helper column that returns the number of that cell's row
- [Max AF Row Number] is a hidden helper column that returns the row number of the latest row to have a date in its [Actual Finish] column
- [Parent-Child Test] is a hidden helper column that returns a 1 for all Parent rows and a 0 for all Child rows
This part works fine. However, it’s limited to a straight waterfall project where each row has a FS dependency on the row above it. Since projects don’t work like that in reality, I need to get my formula to somehow factor in dependencies. What would be the best way to accomplish this? Thanks in advance for any advice.
