Find last value added in a row

Raquel

I'm creating a helper column to always capture the latest value in a row. So far the closest I've gotten is to use JOIN and COLLECT to display all not blank cells. I would love to just show the last cell with data, in this case "Balance at Aug close" so that I can use this column for my dashboard and not have to updated the column every month. I have account information on the left and months across the top. Any ideas are greatly appreciated!

Kelly Moore

Hello @Raquel

This formula will work assuming that there are no blank cells in between columns- for example in your example above, there couldn't be a blank in [JULY] then have a value in [AUG]. The data needs to populate and fill completely from left moving right.

We will start with the first column in your range - [Balance at JULY close] in your example. If the range actually starts earlier, be sure to start with that first column. Since we are dealing with contiguous columns, we can use the INDEX formula and capitalize on the optional column_index portion of the formula. The Count function counts the number of non-blank cells in the range and uses this value as that column index. Since we are looking across the current row, we can fill in the number '1' as the row_index. This is the syntax of the Index function: INDEX(range, row_index, [column_index])

=INDEX [Balance at July close]@row:[Balance at JUNE close]@row, 1, COUNT([Balance at July close]@row:[Balance at JUNE close]@row))

Kelly Moore

Hello @Raquel

This formula will work assuming that there are no blank cells in between columns- for example in your example above, there couldn't be a blank in [JULY] then have a value in [AUG]. The data needs to populate and fill completely from left moving right.

We will start with the first column in your range - [Balance at JULY close] in your example. If the range actually starts earlier, be sure to start with that first column. Since we are dealing with contiguous columns, we can use the INDEX formula and capitalize on the optional column_index portion of the formula. The Count function counts the number of non-blank cells in the range and uses this value as that column index. Since we are looking across the current row, we can fill in the number '1' as the row_index. This is the syntax of the Index function: INDEX(range, row_index, [column_index])

=INDEX [Balance at July close]@row:[Balance at JUNE close]@row, 1, COUNT([Balance at July close]@row:[Balance at JUNE close]@row))

Raquel

@Kelly Moore - thank you so much! I was able to get the formula to work on my sheet. I only wish I had asked fore help sooner instead of fumbling around for hours. Thank you again!