Spreadsheet help please

[Icosahedron]

Can someone assist my Excel-fu?

I want to return a value equal to the last value in a range. Is there a function or set of functions that will do this?

eg I have a list of values in column H. However, the spreadsheet is a working document, so I don't know how many entries column H will contain at any given time, but I want to enter the last nonzero value from column H into cell C1.

I can get the thing to show me the highest or lowest value, but not the last value.

Cheers.

 

[dean]

=INDEX(H1:H9999,(SUM(COUNT(H1:H9999)+1)))

The "+1" at the end assumes you shifted the first value of column H down one row. Delete or change this value to match your need.

Example:

 

[Icosahedron]

INDEX function. Brilliant. I'd figured I might have to use Count or CountA somewhere, but I'd missed Index.

Thanks Dean.

 

[FreeTrav]

If there is guaranteed that there will be no blank cells in the range, use

=INDIRECT("H"&COUNTA(H:H))

If there may be blank cells in the range, use

=INDIRECT("H"&MAX(ROW(1:65535)*(B1:65535<>"")))

 

[Icosahedron]

Thanks guys,

I ended up using

INDEX(H:H,COUNTA(H:H))

Which seemed to do the trick.