Get the latest value of multiple matching entries.
Looking for the right formula to look at the list of entries and IF the Contract Number AND Invoice Line Item Number match. The example screenshot shows 4 lines. They're all the same contract, but the 4th line item is a duplicate entry of "A". SO I want the "Latest Contract Amount" column to keep the latest value entered for those matching rows. In this case Rows 1 and 4 would show the amount entered last on the 19th, of $5000.00.
Best Answer
-
I can't edit my comment, so adding this one:
Create a column called Helper, and use this formula: =[Contract Number]@row + [Invoice Line Number]@row
This formula combines your Contract Number and your Invoice Line Number. Once you create this, you can hide it.
Create a column called Max (or whatever) and use this formula: =MAX(COLLECT([Contract Amount]:[Contract Amount], Helper:Helper, Helper@row))
What this formula does is Collect the Contract Amounts from the Contract Amount column where the record's value is the same as the value in the Helper column. Imagine Smartsheet stores this in its memory, and then the Max asks Smartsheet to tell you which one is biggest.
Answers
-
Answering quick because I need to run, but have you explored using Collect and Max?
Create a helper column to concatenate Contract Number AND Invoice Line Item Number, Collect those values, and use Max.
-
I can't edit my comment, so adding this one:
Create a column called Helper, and use this formula: =[Contract Number]@row + [Invoice Line Number]@row
This formula combines your Contract Number and your Invoice Line Number. Once you create this, you can hide it.
Create a column called Max (or whatever) and use this formula: =MAX(COLLECT([Contract Amount]:[Contract Amount], Helper:Helper, Helper@row))
What this formula does is Collect the Contract Amounts from the Contract Amount column where the record's value is the same as the value in the Helper column. Imagine Smartsheet stores this in its memory, and then the Max asks Smartsheet to tell you which one is biggest.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!