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

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?
 
T

T. Valko

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)
 
R

Rodney

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...
 
S

Shane Devenshire

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.
 
A

austris.bahanovskis

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?
 
T

T. Valko

=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula (or code) return the largest number of duplicates in a list. 16
Sort column B but keep value in column A with value column B 2
find last value in list 8
Select the last value from a list 7
Is this function supposed to work? 6
custom data validation on cells with data validation values 2
LOOKUP VALUE OCCURS >ONCE IN TABLE ARRAY - RETURN LAST OCCURRENCE 6
if condition combined with SMALL 3

Top