cell value in last row of column

Billm

Board Regular
Joined
Mar 19, 2002
Messages
88
This is driving me nuts .. I have a 2-column set of numbers cols (G-H)and the number
of rows in the column is varied by a user macro - so I know how many rows are in the column

I want cell C10 to always display the value of the last row in the column using formula (not VBA)

I know this is simple .. but its driving me mad .. what is the formula to get the contents of a cell in column H row X - where x = the number of rows the user has chosen

thanks
Bill
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
ok .. I worked it out .. I have the macro store the calculated last cell in the worksheet, eg C10 and then the formula
=Indirect(C10) can go and get the value that I need.

but ..how can I use variables within the "indirect" formula rather than a hard coded cell reference ?
Thanks Bill
 
Upvote 0
Hi,

Used a named formula which adjusts dynamically and you won't need to worry about the indirect referencing.

Insert>Name>Define
e.g "Last_Cell_in_G"

Refers to:
=OFFSET(Sheet1!$G$1,MATCH(9.9E+307,Sheet1!$G:$G,1)-1,0,1,1)

This will find the last numeric value in the column.

Change the RefersTo formula to
=OFFSET(Sheet1!$G$1,COUNTA(Sheet1!$G:$G)-1,0,1,1)

and you will get the last cell value (text or numeric). This requires that there is a continuous range (no gaps in the column).

You can shorten the name to make it easier (CLast, for example).

HTH,
Jay
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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