Last entry in a column

[bubba]

Is there any way to write a formula to send the last numerical entry in
a column (range?) to a designated cell, _without using VBA or other
programming_?

1
2
3
4

Therefore, send the contents of the last cell (4) to designated cell (C1)


Next day (after an entry) the column might be:

1
2
3
4
5

Therefore, send the contents of the last cell (5) to designated cell (C1)


Note: The column might eventually be 200+ rows long.


Thanks,

John

Mac OSX 10.4.11
Excel 11.3.6

 

[Roger Govier]

Hi John

In C1
=LOOKUP(99^99,A:A)

Basically the number to be looked up must be larger than the highest
number you are likely to find in the column.

 

[Ron Rosenfeld]

Is there any way to write a formula to send the last numerical entry in
a column (range?) to a designated cell, _without using VBA or other
programming_?

1
2
3
4

Therefore, send the contents of the last cell (4) to designated cell (C1)


Next day (after an entry) the column might be:

1
2
3
4
5

Therefore, send the contents of the last cell (5) to designated cell (C1)


Note: The column might eventually be 200+ rows long.


Thanks,

John

Mac OSX 10.4.11
Excel 11.3.6

=LOOKUP(2,1/(LEN(A:A)>0),A:A)

If your version of Excel is prior to 2007, then you cannot reference the entire
column, so change the column reference to a maximum of A1:A65535.

Smaller column references should result in faster calculations, so you could
also change both of them to A1:A200+

--ron

 

[helene and gabor]

Hello,

Assuming you want to copy the last entry of column A into C1 enter this
formula into C1 and execute as an array formula:Shft,Ctrl,Ent

=INDIRECT(ADDRESS(MAX((ROW(1:500)*(A1:A500<>""))),COLUMN(A:A)))



Best Regards,


Gabor Sebo

 

[Ron Rosenfeld]

=LOOKUP(2,1/(LEN(A:A)>0),A:A)

If your version of Excel is prior to 2007, then you cannot reference the entire
column, so change the column reference to a maximum of A1:A65535.

Smaller column references should result in faster calculations, so you could
also change both of them to A1:A200+

--ron

I misread. YOu wanted the last *NUMERICAL* entry. The above will give the
last entry, which might be alpha.

For last numerical entry: =LOOKUP(2,1/(ISNUMBER(A:A)),A:A)

or, simpler: =LOOKUP(1E+307,A:A)

--ron

 

[bubba]

Roger,

Thanks! A nice concise answer. It worked like a charm, the first time.
Have always been puzzled as how to do this. I had to make formula number
bigger (999999), as my real numbers ($xxx,xxx.xx) were much bigger than
my example.

Although I'm not sure how I would use it, I note that one can also look
at multiple columns by modifying formula to _=LOOKUP(99999,A:B)_

Also, thanks to Ron and gabor who also offered solutions as well.

A most excellent set of replies!

Will be sorry to see this forum disappear.

Best regards.

John
***********

 

[Roger Govier]

Hi John

Glad it worked out for you.
The number I posted was 99 ^ 99 which is 99 to the power of 99 or
3.6973E+197

For some reason the newsreader has displayed literally as one would have
something to he power, but that perhaps was misinterpreted by you as 9999