Formula to return the most recent entry in a column
I want to have the most recent status entered in a given column roll-up. Essentially for Strategy 3, Step 1 (monthly) column the Total in row 2 should read "In Progress" to match the most recent report submitted in August. I am struggling with the best formula to do this. The most recent status isn't always linear so it would be helpful to have the most recent entry roll up. I am hoping there is a simple formula to do this.
Also note, that the data in these cells pulls from another sheet using index collect.
Best Answer
-
Hello @Melissa Guthrie CDA!
This would be how I'd tackle this solution. I'd create a Auto Number Column [Row ID], and a Helper Column [Row #] with a column formula =MATCH([Row ID]@row, [Row ID]:[Row ID], 0).
Then I'd INDEX your [Strategy 3, Step 1 (monthly] column finding what the MAX [Row #] is where [Strategy 3, Step 1 (monthly] is not blank. I'm using the [Row #] column instead of [Row ID] as the [Row #] will not change in the case of rows being added, rearranged, or deleted.
So the formula I'd use in [Strategy 3, Step 1 (monthly)]2 would be:
=INDEX([Strategy 3, Step 1 (monthly)]:[Strategy 3, Step 1 (monthly)], MAX(COLLECT([Row #]:[Row #], [Strategy 3, Step 1 (monthly)]:[Strategy 3, Step 1 (monthly)], <>"")))
If you prefer to INDEX based on time, I'm sure you can find the MAX Modified Date. I do caution against this however, since the modified date function will get triggered by any changes made in that row even if made in another column and may pull in the incorrect values.
I hope this helps!
Davin Vo - Sevan Technology
Smartsheet Platinum Partner
Answers
-
Hello @Melissa Guthrie CDA!
This would be how I'd tackle this solution. I'd create a Auto Number Column [Row ID], and a Helper Column [Row #] with a column formula =MATCH([Row ID]@row, [Row ID]:[Row ID], 0).
Then I'd INDEX your [Strategy 3, Step 1 (monthly] column finding what the MAX [Row #] is where [Strategy 3, Step 1 (monthly] is not blank. I'm using the [Row #] column instead of [Row ID] as the [Row #] will not change in the case of rows being added, rearranged, or deleted.
So the formula I'd use in [Strategy 3, Step 1 (monthly)]2 would be:
=INDEX([Strategy 3, Step 1 (monthly)]:[Strategy 3, Step 1 (monthly)], MAX(COLLECT([Row #]:[Row #], [Strategy 3, Step 1 (monthly)]:[Strategy 3, Step 1 (monthly)], <>"")))
If you prefer to INDEX based on time, I'm sure you can find the MAX Modified Date. I do caution against this however, since the modified date function will get triggered by any changes made in that row even if made in another column and may pull in the incorrect values.
I hope this helps!
Davin Vo - Sevan Technology
Smartsheet Platinum Partner
-
Thank you so much, Davin! That worked perfectly. I appreciate your help :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!