Select only most recent rows using LARGE?
I thought I would post my issue here since the SS community has been so great at helping solve my other SmartSheet dilemmas!
For each project that the PMO oversees, we have a sheet with a single row of project health metadata. On the first of each month a workflow runs against the metadata sheet and copies the row to a “scorecard” sheet so we have a static “image” of the data. So at the end of the year, it is possible to have up to 12 rows in the scorecard sheet. The period/month and workflow run dates are recorded each month (example below).
I want to create a report from the scorecard sheet, but only show the most recent 3 months. In the example below, I'd like to only show the data from months 2, 3 and 4. And in month 6, only show data from periods 4,5 and 6.
Would the LARGE function work if I created a helper row and scanned the period column and added some text in the helper column for the 3 largest periods? And what would the formula look like? I have never used LARGE.
As always, any advice or help would be appreciated.
Best Answer
-
Issue solved. The Period field was copied from another sheet using workflow. For some odd reason, the Period field in the originating sheet had a leading non-numeric character which the LARGE function could not interpret. When I cleaned up the source data, the formula works exactly as planned.
Answers
-
Update: Here is the formula I am using, but the results are not what I was hoping for.
=IF(Period@row = LARGE(Period1:Period12, 1), 1, IF(Period@row = LARGE(Period1:Period12, 2), 2, IF(Period@row = LARGE(Period1:Period12, 3), 3, 0)))
Here are the results:
And this is what I want to get:
-
Issue solved. The Period field was copied from another sheet using workflow. For some odd reason, the Period field in the originating sheet had a leading non-numeric character which the LARGE function could not interpret. When I cleaned up the source data, the formula works exactly as planned.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!