Collect Most Current Per Project

Tim Hanson
Tim Hanson ✭✭✭
edited 01/05/23 in Formulas and Functions

We build out contracts and contract MODs on a spreadsheet, the single spreadsheet contains all contracts and MODs across all projects. I'm needing to create a column with a function that reports the most current difference between the original contract and sum of any MODs, per project, per company.

Columns involved so far...

one column contains a simple function that displays the difference between the original contract value and the sum of any MODs to that point (the date that MOD was created).

a project column, that defines which project that contract is for.

a company column, that defines who the contract/MOD is with.

and a created date column.

Here is what I've come up with, but the syntax isn't even working... thoughts? suggestions?

=INDEX(COLLECT([Difference From Original Contract]:[Difference From Original Contract], Project:Project, =Project@row, (Created:Created, =MAX(Created:Created)), (Company:Company, =Company@row)),1)

Best Answer

  • Tim Hanson
    Tim Hanson ✭✭✭
    Answer ✓

    I was approaching this all wrong. Here is the solution I found.

    =IF(MAX(COLLECT(Created:Created, Project:Project, Project@row, Company:Company, Company@row)) = Created@row, [New Contract Sum]@row - [Original Contract Amount]@row, "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!