Formula to return the most recent entry in a column
I want to have the most recent status entered in a given column rollup. 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.1K Get Help
 349 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!