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

BWheel

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

Find more posts tagged with

Formulas

Support

Conditional Formatting

Accepted answers

BWheel

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!

All comments

Paul Newcome

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

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

"<>"

Is this correct?

BWheel

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

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

"<>"

<> ""

BWheel

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

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

BWheel

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

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

BWheel

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.