Collect Most Current Per Project

Tim Hanson

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)

Tim Hanson

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, "")

Tim Hanson

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, "")

Genevieve P.

Hi @Tim Hanson

Thanks for sharing your solution! I'm glad you were able to figure out a formula that works for you.

Cheers,

Genevieve