how do I get the last entered value in a column of values

  • Thread starter Thread starter Rodney
  • Start date Start date
R

Rodney

I am creating a list (column) with financial values in it.
I want to know how to reflect the last entered value, not necessarily the
largest or smallest value, on the list in a seperate cell on another
worksheet?
 
I am creating a list ... with financial values

I'm assuming financial values are numbers.

This will return the *last* number from Sheet1 column A:

=LOOKUP(1E100,Sheet1!A:A)
 
Thanks. That was quick.. Great help

T. Valko said:
I'm assuming financial values are numbers.

This will return the *last* number from Sheet1 column A:

=LOOKUP(1E100,Sheet1!A:A)
Click to expand...
 
Hi,

If you are using 2007, the formula can be shortened to:

=LOOKUP(2,1/(Sheet1!B:B<>""),Sheet1!B:B)

Or better yet if you name the range B:B "B" then

=LOOKUP(2,1/(B<>""),B)

In fact in almost all cases where you reference another sheet it pays to
range name the references because you don't need the "Sheet1!" portion on the
formula.
 
Sorry for jumping in here but could you explain why this, e.g.:
=LOOKUP(1E100,Sheet1!A:A) or
=LOOKUP(2;1/(Sheet1!A2:A100<>"");Sheet1!A2:A100)
work?
Both are elegant solutions but I'm trying to understand why the
lookups returns the last nonempty cell in the range?
 
=LOOKUP(1E100,Sheet1!A:A) or
=LOOKUP(2;1/(Sheet1!A2:A100<>"");Sheet1!A2:A100)
why the lookups returns the last nonempty cell in the range?
Click to expand...

Note that those two formulas do different things.

The first formula will only return the last *numeric* value in the range.

The second formula will return the last value in the range if it's one of
the following:

a numeric value
a text value (except an empty text string "" which might be the result of
another formula)
any error value
the logical values TRUE or FALSE
 
Back
Top