Return the last number typed in a column

[ADRIAN2]

Dear Folks;

I have a column of numbers, I wish to have the last number entered always shown in a seperate table. How do I accomplish this with excel formulas.

Best Regards

Adrian D.

[DRJ]

Hi - Welcome to the board

If the column is A then

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A),1)

[Damon Ostrander]

Hi Adrian,

I'm not aware of a built-in worksheet function that does this. So here is a user-defined function (UDF) that adds this capability to Excel:

Function LastVal(InRange As Range) As Variant
'returns the last value in the first column of InRange
With InRange.Columns(1)
If IsEmpty(.Cells(.Cells.Count)) Then
LastVal = .Cells(.Cells.Count).End(xlUp).Value
Else
LastVal = .Cells(.Cells.Count).Value
End If
End With
End Function

To use this you would do something like:

=LastVal(B:B)

to return the last value in column B, or

=LastVal(B5:B200)

to return the last non-empty cell in B5:B200.

To install this UDF simply go to the Visual Basic Editor (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste the above code into the Code Pane. It will be immediately available for use from any worksheet in your workbook.

[Brian from Maui]

Originally Posted by ADRIAN2

Dear Folks;

I have a column of numbers, I wish to have the last number entered always shown in a seperate table. How do I accomplish this with excel formulas.

Best Regards

Adrian D.

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

=LOOKUP(9.99999999999999E+307,Sheet1!A:A)

caveat, will return #N/A if no values are in the column and will return only numerica vales

=INDEX(Sheet1!G:G,MATCH(REPT("z",255),Sheet1!G:G))

=LOOKUP(REPT("z",255),Sheet1!G:G)

will return text

Edit,

Geez, I'm slow. As an alternative using Morefunc addin, this returns either text or numeric

=LASTROW(A:A)