How to return most recent value when conditions met. Smartsheet Support is Stumped

BWheel
BWheel ✭✭
edited 06/13/23 in Formulas and Functions

Hello,

I stumped Smartsheet Support with this formula question:

I want to return the most recent approval status from another column based on common Project ID.

I have four columns:

Created Date - Project ID - Enter Status - Project Approval Status (Where the formula exists)

All the columns are text except for Created Date, which is Date.

My current formula is:

=IF(ISBLANK([Enter Status]@row), VLOOKUP([Project ID]@row, [Project ID]:[Enter Status], 4, false), [Enter Status]@row)

Unfortunately, this returns the first instance of approval status and not the latest one. I believe the solution involves some combination of MAX and COLLECT... There are some brilliant people in this community. Can any of you see a solution where Smartsheet Support cannot?

Thank you

Best Answer

  • BWheel
    BWheel ✭✭
    Answer ✓

    I finally got it.

    I added the Max Date (Called "LastDate") as a helper column, with your help on the formula:

    =MAX(COLLECT([Created Date]:[Created Date], [Project ID]:[Project ID], [Project ID]@row, [Pr Ap Sta]:[Pr Ap Sta], <>""))

    Then, under "Project Approval Status", I used INDEX(COLLECT (From another topic Paul answered) to pull the correct status from most current:

    =INDEX(COLLECT([Pr Ap Sta]:[Pr Ap Sta], [Project ID]:[Project ID], [Project ID]@row, [Created Date]:[Created Date], LastDate@row), 1)

    And there's my answer. Thank you, Paul!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!