MAX Formula Help

Kelly Pratt

I am working on a max formula that I can't seem to figure out how to set up the criteria.

Background:

I have a sheet (Intake Sheet) that is gathering project health data on a weekly basis via update request. Each week after it is collected, every row gets copied to another sheet (Historical Intake) so always have a historical copy of what the health was each week.

I am trying to write a formula on the Intake sheet that looks at the Historical intake to pull in the previous week's health to see a comparison. I know I can use the Created Date to pull in the most recent date, but I also have to look at the project ID to match, and with the goal of getting the health RYG returned.

Here is what I have:

=MAX(COLLECT({Intake Sheet Historical Project Health}, {Intake Sheet Historical Created}, "THIS IS WHERE I"M NOT SURE WHAT TO PUT", {Intake Sheet Historical Trends ID}, ID@row))

how do I put in the collect formula to collect the most recent?

Or do I have this formula all wrong and is there a better way to do it?

Thanks in advance!

Paul Newcome

Try this instead:

=INDEX(COLLECT({RYG Column}, {Project ID Column}, @cell = ID@row), COUNTIFS({Project ID Column}, @cell = ID@row)

Paul Newcome

Try this instead:

=INDEX(COLLECT({RYG Column}, {Project ID Column}, @cell = ID@row), COUNTIFS({Project ID Column}, @cell = ID@row)

Kelly Pratt

@Paul Newcome - thanks for the quick response! That did not work - i got an error of #invalid column value - I also don't understand how this would pull in the most recent as it's not taking into account the created date?

Am I missing something?

Paul Newcome

Make sure you are putting it into a column formatted for RYG.

The INDEX function works by first specifying where you want to pull from then specifying which row you want to pull from (we don't need the third portion in this instance).

So we use the COLLECT to gather up all of the RYGs from the copy sheet and create an array (on the back-end) based on the ID. We know that the copy row automation always adds the new row to the bottom of the sheet, so we can assume that the lowest entry for that ID is the most recent entry for that ID. So if we use the COUNTIFS to count how many times that ID is found in the copy sheet, that tells us what number within the array we want to pull.

Very basic example.

ID . . . . . RYG

1 . . . . . R

2 . . . . . G

1 . . . . . Y

In the above, the COLLECT function would pull together the following array from the RYG column for the ID of 1:

RY

We then use the COUNTIFS to count how many times the ID of 1 shows up. That gives us 2.

Finally we use that 2 to tell the INDEX function to pull the 2nd entry from the array generated by the COLLECT function. This gives us "Y". Since the copy row automation drops new rows at the bottom of the sheet, we know that this is the most recent entry.

I actually use this very same setup quite frequently, so I know it can work. The only thing I missed in my above example would be one more closing parenthesis at the very end of the formula, but Smartsheet would automatically add that in anyway.

Kelly Pratt

Thanks for the feedback and explanation @Paul Newcome ! It looks like if there isn't any or enough data yet in the historical sheet (where rows are copied) it shows up as #INVALID COLUMN TYPE - as soon as there's enough rows added, then it works well.

Paul Newcome

Yes. I usually use an IFERROR for that, but I like to leave it off until I know it is working as expected so I can troubleshoot when needed.

=IFERROR(INDEX(COLLECT({RYG Column}, {Project ID Column}, @cell = ID@row), COUNTIFS({Project ID Column}, @cell = ID@row)), "")