# 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.

• ✭✭✭

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

• ✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!