Returning contents of Last Used Cell in Column

I/O_Dork

Board Regular
Joined
Jul 15, 2004
Messages
75
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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
Code:
With Columns(myColumn).SpecialCells(xlCellTypeFormulas)
    With .Areas(.Areas.Count)
        MsgBox .Item(.Cells.Count).Address
    End With
End With
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top