Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

✭✭
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

  • ✭✭
    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!

Trending in Formulas and Functions