Trouble with multiple checks using MAX/COLLECT

I have a set of data for monthly KPI's. The user may enter the same KPI with a different dollar value multiple times. Once I identify the last entry for each combo of calendar month, fiscal year and metric, I will use that data on a report to show the user the value that we'll include later down the road on another report. Even if it's December 2024, the user may enter a new value for July 2024.

Here's a sample of the data - 'metric' names changed for security reasons:

I have the following calculation in the 'Last Entry X' column but as you can see, I'm getting an error:

=MAX(COLLECT([Created On]:[Created On], [Month Number]:[Month Number], [Month Number], [Fiscal Year]:[Fiscal Year], [Fiscal Year], Metric:Metric, Metric@row))

I'm not sure what I'm doing wrong. I want this column to return the maximum created on date for the combo of calendar month, fiscal year and metric. The 'Last Entry X' column is defined as a date column. Any ideas?

Tags:

Best Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓

    Hello @hsheridan,

    The #UNPARSEABLE error is coming from some references to cells without a row # specified. You must always reference a column, range, or individual cell using @row or a # in your formulas. I've added @row in bold to your formula where it is missing. I assume you are wanting to reference these columns in the same row as your formula.

    =MAX(COLLECT([Created On]:[Created On], [Month Number]:[Month Number], [Month Number]@row, [Fiscal Year]:[Fiscal Year], [Fiscal Year]@row, Metric:Metric, Metric@row))


    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • hsheridan
    hsheridan ✭✭
    Answer ✓

    Aha! That did the trick. Thank you so much Dan!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!