how to reference a changing 'last row' cell in a formula
 

I've done this in a vba macro, but now I have a need to do it in a
cell formula.

In my worksheet, a new row of detail is added daily. In the 'header'
area (rows 1 through 10) I have a cell that needs to make a
calculation based on the value of a cell in the most recently added
(last occupied) detail row. In pseudo code it would be something like
this:

=$B(LastRow) * 23.5

Is there an elegant way to do this?

TIA

how to reference a changing 'last row' cell in a formula
 

=LOOKUP(1E+100,B:B) * 23.5


Gord Dibben MS Excel MVP


On Fri, 6 Jun 2008 17:11:31 -0700 (PDT), EdStevens
wrote:

I've done this in a vba macro, but now I have a need to do it in a
cell formula.

In my worksheet, a new row of detail is added daily. In the 'header'
area (rows 1 through 10) I have a cell that needs to make a
calculation based on the value of a cell in the most recently added
(last occupied) detail row. In pseudo code it would be something like
this:

=$B(LastRow) * 23.5

Is there an elegant way to do this?

TIA

how to reference a changing 'last row' cell in a formula
 

For Column B...

=23.5*LOOKUP(2,1/(A1:A65535<""),A:A)

Rick


"EdStevens" wrote in message
...
I've done this in a vba macro, but now I have a need to do it in a
cell formula.

In my worksheet, a new row of detail is added daily. In the 'header'
area (rows 1 through 10) I have a cell that needs to make a
calculation based on the value of a cell in the most recently added
(last occupied) detail row. In pseudo code it would be something like
this:

=$B(LastRow) * 23.5

Is there an elegant way to do this?

TIA