cell value in last row of column

Billm · Apr 26th, 2002, 05:15 AM

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

Billm · Apr 26th, 2002, 05:46 AM

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

Aladin Akyurek · Apr 26th, 2002, 07:37 AM

In C10 enter:

=MATCH(9.99999999999999E+307,G:G)

Jay Petrulis · Apr 26th, 2002, 07:39 AM

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

Billm · Apr 26th, 2002, 11:45 AM

hey .. it works great ..
thanks
Bill

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 05:15 AM	#1
Billm Board Regular Join Date: Mar 2002 Posts: 83	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

Apr 26th, 2002, 05:46 AM	#2
Billm Board Regular Join Date: Mar 2002 Posts: 83	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

Apr 26th, 2002, 07:37 AM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	In C10 enter: =MATCH(9.99999999999999E+307,G:G)

Apr 26th, 2002, 07:39 AM	#4
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	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

Apr 26th, 2002, 11:45 AM	#5
Billm Board Regular Join Date: Mar 2002 Posts: 83	hey .. it works great .. thanks Bill