Last value in any column

[Merlinwarrior]

Good day,
I need either a function or macro that will give me the last numeric value of any column of data. I have read the threads related to "=LOOKUP(2,1/(A:A<>""),A:A)."
However the above limits me to column A and I don't have the skills to change it to give me what I need from other columns.

The purpose of use is for a dashboard. I need to extract totals from many spreadsheets every day, to just one sheet with the = function in cells on that dashboard. The intention is to paste the volumes of data into many tabs and then just pull the totals to the one dashboard sheet.

Apologies for the long explanation of the purpose.

Thank you!

 

[James006]

Hello,

When you are saying

last numeric value of any column of data

Do you mean last numeric value in ALL your Columns ...?

 

[Aladin Akyurek]

Last numeric value...

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

not the one you mention.

 

[Merlinwarrior]

It worked!
Thank you very much Aladin.

Last numeric value...

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

not the one you mention.

 

[Merlinwarrior]

Thanks James. Just 1 column per spreadsheet.

 

[Kamolga]

By naming the range or using table, you can get to the last column.

Else there is a way to use a udf like this one

Function LastNum()
Dim LastColumn As Long
Dim LastRow As Long
Dim Ctr As Long
    With ActiveSheet
        LastColumn = .UsedRange.Columns.Count
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
    End With
    For Ctr = LastRow To 1 Step -1
        If IsNumeric(Cells(Ctr, LastColumn).Value) = True Then
            LastNum = Cells(Ctr, LastColumn).Value
            Exit Function
        End If
    Next Ctr
End Function

and then typing this is in sheet

=LastNum()

 

[strooman]

Row\Col	A​	B​	C​	D​
1​	Data	Result	Formula	Task
2​	1​	99​	=LOOKUP(9,99999999999999E+307;A2:A14)	Get value last number
3​	300​	Peter​	=INDEX(A2:A14;MATCH(REPT("z";255);A2:A14))	Get value last tekst
4​	400​	99​	=INDEX(A2:A14;MATCH(9,99999999999999E+307;A2:A14))	Get value last number
5​	500​	Peter​	=LOOKUP(2;1/(LEN(A2:A14)>0);A2:A14)	Get value last tekst/number
6​	Chantal​	Peter​	=LOOKUP(REPT("z";255);A2:A14)	Get value last tekst
7​	1,00E+307​
8​	1​
9​	3​
10​	5​
11​	7​
12​	Mary​
13​	99​
14​	Peter​

 

[Aladin Akyurek]

It worked!
Thank you very much Aladin.

You are welcome.