Returning the last entry in a column?

Graham Rimell

Hi,

I'm looking for a way to return the bottom row of column that is not the same level as the other columns.

I want to return the ones highlighted in red in another box, however because there is blank spaces between them and the bottom of the other columns I'm struggling to get a formula that works.

It seems like it should be simple to do!

Thanks

Paul Newcome

You could insert a "Helper Column" that can be hidden after setup that essentially replicates the row numbers.

=COUNTIFS(Ratio$1:Ratio@row, OR(@cell = "", @cell <> ""))

Then to pull the data you want you can use an INDEX to pull and a MAX/COLLECT to grab the highest row number where Total is not blank and use that for the row number in the INDEX column.

=INDEX(Total:Total, MAX(COLLECT([Row Number]:[Row Number], Total:Total, @cell <> ""))

Graham Rimell

I've managed to find a sort of solution using the Report function. I created a Row Report, and set one of the columns to show only 'Greater than 0', It filtered out all the blanks leaving just the filled in data.

Paul Newcome

How are you returning the single line from the report?

Graham Rimell

I didn't in the end, but It gave me a way to display the filtered data in a graph, which is good enough for what I need at the moment!