If somebody could help me with this I would be very happy, I can't figure it
out:
In a sheet I have a column of figures to which new figures are added regularly.
At the side of the column I'd like to have a cell containing the last number in
the column so I can see that in one go without having to scroll to the end of
the column.
I tried all sorts of stuff but cannot get it to work, all help would be highly
appreciated!
Thanks
Dries

If your numbers are in column A, then put this formula in B1 (or
somewhere not in the same column):

=LOOKUP(10^10,A:A)

Hope this helps.

Pete

On Feb 1, 11:23*am, dries <dr...@bessels.nospam.org> wrote:
> If somebody could help me with this I would be very happy, I can't figure it
> out:
> In a sheet I have a column of figures to which new figures are added regularly.
> At the side of the column I'd like to have a cell containing the last number in
> the column so I can see that in one go without having to scroll to the endof
> the column.
> I tried all sorts of stuff but cannot get it to work, all help would be highly
> appreciated!
> Thanks
> Dries

On Feb 1, 11:23 am, dries <dr...@bessels.nospam.org> wrote:
> If somebody could help me with this I would be very happy, I can't figure it
> out:
> In a sheet I have a column of figures to which new figures are added regularly.
> At the side of the column I'd like to have a cell containing the last number in
> the column so I can see that in one go without having to scroll to the end of
> the column.
> I tried all sorts of stuff but cannot get it to work, all help would be highly
> appreciated!
> Thanks
> Dries

Lets say your column of figures is A and you want the result showing
in B1. Type the following function into B1:-

=OFFSET(A1,COUNTA(A:A)-1,0)

and that should do the trick.

Hope this helps,
Matt Richardson
http://teachr.blogspot.com

01 Feb 2008 11:23:40 GMT from dries <(E-Mail Removed)>:
> In a sheet I have a column of figures to which new figures are
> added regularly. At the side of the column I'd like to have a cell
> containing the last number in the column so I can see that in one
> go without having to scroll to the end of the column.

http://www.contextures.on.ca/xlNames01.html#Dynamic
gives the technique for a dynamic name that refers to the whole
column. You can easily modify that to a dynamic name that contains
only the last filled element in the column.

> I tried all sorts of stuff but cannot get it to work, all help
> would be highly appreciated!

Just once when somebody says something like this, I wish they would
tell specifically what they've tried.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

"Matt Richardson" <(E-Mail Removed)> wrote...
....
>Lets say your column of figures is A and you want the result showing
>in B1. Type the following function into B1:-
>
>=OFFSET(A1,COUNTA(A:A)-1,0)
>
>and that should do the trick.

This fails when there are blank cells above or nonnumeric cells below the
last number in column A. Also, OFFSET is volatile, so it recalculate more
often than necessary. Whenever your OFFSET formula would return the correct
result, so would

=INDEX(A:A,COUNTA(A:A))

but this INDEX formula calls only nonvolatile functions. However, the LOOKUP
formulas other respondents suggested is always better because it recalcs
faster and is more robust, ALWAYS returning the last number in the column no
matter what else is in the column.