Returning contents of Last Used Cell in Column

[I/O_Dork]

Hi all:

I know how to write a formula to return the contents of the last used cell in a given column or range, but the issue here is that I don't just want to look for the actual contents of the last cell but the results of the formula in the last used cell.

For example, if I have a formula that occupies the cells in column R (or say out to row 500) and my last logical [true] formula result occurs on R10 not R500... I want the offset or index formula to return the last cell based on the formula result, not that fact that its the last cell that has anything in it or even a formula. How do I accomplish this. Currently, my offset formula is returning cell R500 b/c that is the last cell with content and not R189 which has a formula result of $99,000.00. The column is formated for number(accounting) as the formulas in column A return the dollar amount invested.

Here is my "find last used cell" formula:
=OFFSET(Blotter!$R$1,MATCH(MAX(Blotter!$R:$R)+1,Blotter!$R:$R,1)-1,0)


Any input is much appreciated!

Brian

 

[Arul.rajesh]

Is your data in column "R" sorted?
why do you use an approximate match?
also why did you add 1 to the maxvalue in column R when you were doing an approximate match anyway?
Does column "R" contain a combination of formulas and constants?
Do you want to find the last row in column "R" which has the formula?

 

[mikerickson]

As I understand it, you have a formula (somewhere) and based on the results of that formula, you want to look at the "last cell based on that formula result".

Your request is not clear.

Given a column, are you looking for the last cell in that column that contains a formula?
In VBA, that would be

With Columns(myColumn).SpecialCells(xlCellTypeFormulas)
    With .Areas(.Areas.Count)
        MsgBox .Item(.Cells.Count).Address
    End With
End With