Finding the earliest date for each vendor

Celia Gust

I have a list of contracts that need to be audited periodically. I want to put a column into the list that will show the earliest date required to do the audit based on the vendor and contract expiration dates. In other words, if Vendor A has 10 contracts with expiration dates over the next 5 years, what is the earliest expiration date (MIN)? That will be the audit date of all contracts for that vendor. The chart below shows what I am looking to do. I think I need the MIN formula for the Expiration date column, but I can't figure out how to say the range is in the Vendor column. I'm fairly new to Smartsheet and appreciate the help!

Paul Newcome

You will need a MIN/COLLECT like so:

=MIN(COLLECT([Expiration Date]:[Expiration Date], Vendor:Vendor, @cell = Vendor@row))

Paul Newcome

Happy to help. 👍️

Paul Newcome

You will need a MIN/COLLECT like so:

=MIN(COLLECT([Expiration Date]:[Expiration Date], Vendor:Vendor, @cell = Vendor@row))

Celia Gust

Paul, thank you so much! That worked perfectly once I made the result column a Date column. I learn new things everyday. 😊

Paul Newcome

Happy to help. 👍️