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?
Best Answers
-
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!
-
Aha! That did the trick. Thank you so much Dan!
Answers
-
If the syntax of your formula is as shown it's the [Month Number] and [Fiscal Year] that are causing problems. On their own, they should either be appended by @row (if referencing a column) or a # (if referencing a Sheet Summary field).
-
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!
-
Aha! That did the trick. Thank you so much Dan!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!