Formula Help - Not Sure Where to Post

[tompal]

Hey All,

This is my first post so I'm not to sure where to put it, but here i
goes:

I have a problem with my worksheet that I am trying to create. I'
trying to build a sheet that helps me track my "fantasy" stocks. I wan
to build it myself b.c that's the type of person that I am. My question
is how to i program a cell to display the last cell of a specifi
column. Basially I want cell B1 to display whatever number is in th
last column of H. Meaning if H7 has the last number in it I want B1 t
display H7, but if H8 is the last cell filled I want B1 to displa
that. Is my question clear?

Thanks for the help,

-To

 

[Colin Sandall]

tompal,

Try this:

=OFFSET(H1,COUNTA(H:H)-1,0)

This assumes that you have an entry in every cell in column H between the
top and the bottom.

Regards

Colin

 

[pinmaster]

Can't remember where I got this from but it works great!

=INDEX(B1:B100,MAX((B1:B100>0)*ISNUMBER(B1:B100)*ROW(B1:B100)-ROW($B1))+1)
this is an array formula so it needs to entered using Shift+Ctrl+Enter

HTH
JG

 

[Gord Dibben]

Tom

=LOOKUP(9.99999999999999E+307,H:H) will fetch the last numeric value in col H

Ignores blanks in the column.


Gord Dibben MS Excel MVP

 

[Michael]

Good afternoon Gord. Would you mind explaining why you use that number?
I've seen it in formulae before, but don't have any idea why it is used.
Thanks in advance.