I was using a balance column and just wanted the last value (the balance) to show up in my totals row at the bottom, thereby when I filtered the list the value would still be the outstanding balance for the whole list.
There were a couple of options I found through Googling, but I didn't like any of them as they didn't work well with blank rows.
My resolution was to format the range of cells as a Table with the "Format as Table" option in Excel 2007.
By doing this it creates ranges for each column. My table looks like this:
To get the last value in (C7) for this table I used this formula:
=INDEX([Balance], ROWS([Balance]),1)
By using the Rows function here it gives me the last row within the range I am looking for and then the index provides the value.
