Formula to return the most recent entry in a column

Melissa Guthrie CDA

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.

Davin Vo

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

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!

Melissa Guthrie CDA

Thank you so much, Davin! That worked perfectly. I appreciate your help :)