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

Options
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 ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Using your formula from your other comment:

    =IF(ISBLANK([Pr Ap Sta]@row), (MAXIFS([Created]:[Created],[Project ID]:[Project ID],[Project ID]@row, [Pr Ap Sta], "<>")), [Pr Ap Sta]@row)


    You would replace the MAXIFS with a MAX/COLLECT.

  • BWheel
    BWheel ✭✭
    Options

    Hi Paul - Changing the formula to the following gives me a "Syntax isn't quite right" error.

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

    I have double checked my column names for mis-typing and can't find a problem ('Pr Ap Sta' is the same as 'Enter Status' in original question)

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 06/13/23
    Options

    "<>"

    Is this correct?

  • BWheel
    BWheel ✭✭
    Options

    I am also thinking that my formula cannot accurately return the most recent approval status because the most recent instance of the Project ID is the current row, which is blank.

    If I create a second column to reference the project ID for rows where enter status is not blank, that might solve the problem.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You need to use a full column reference in the bold portion.

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


    You would also need to change

    "<>"

    to

    <> ""

  • BWheel
    BWheel ✭✭
    Options

    Thanks Paul. I must be close, but I am getting #INVALID OPERATION for the formula

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You're missing a comma between the last range and criteria set of the COLLECT function.

  • BWheel
    BWheel ✭✭
    Options

    I also tried it with the comma and it wants to return 0. Then, I tried to change out the "Created Date" with an earlier date just for the blank rows - which I could do with a substitution step, but that didn't change the result.

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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What type of column is your [Created Date] column and how exactly is it being populated?

  • BWheel
    BWheel ✭✭
    Answer ✓
    Options

    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!

  • BWheel
    BWheel ✭✭
    Options

    And to answer your previous question - Created date is auto generated date field marking when the row was added. The problem with my original formula, I believe, was that it was trying to return the max date when I wanted the status from a different field.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!