Excel novice- help required

[Roc]

I have sheet reading external text data updating every second. This is placed in say A1:A3000. I need function/formula in B1 to show latest reading in range (constantly updating). Many thanks in advance.

 

[SteveO59L]

What do you mean by lastest reading ?

 

[Peter_SSs]

Roc

Welcome to the Mr Excel board!

Try =LOOKUP("zzzzz",A:A)

 

[BrianB]

This reads the last entry in column A
=LOOKUP(9.99999999999999E+307,A:A)

 

[Roc]

Apologies for not being clear enough, my first post.
Latest reading = most recent reading from range. Have played with OFFSET but doesn't like blanks.

 

[Peter_SSs]

I take it then that the most recent reading does not go at the bottom of the column list? Where does it go? How would you find the most recent reading if you were doing it manually?

 

[MichaelRo]

I was going to suggest:

=INDIRECT("A"&COUNTA(A:A))

But this will only work if every row is populated down to the last reading you are after.

BrianB's suggestion works perfectly

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

I'll have to remember that one!!

 

[Peter_SSs]

BrianB's suggestion works perfectly

Except that Brian's suggestion will find the last NUMBER in the column, whereas the OP indicated his data is TEXT and therefore my (similar) formula should do the trick provided there isn't any real data starting with more z's than I put in the formula.

 

[BBHRVS]

Hi Roc,

If "reading" means last change in a cell, I suggest to put this into your worksheet module :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then ActiveSheet.Cells(1, 2) = Target.Address & " at " & Date & "/" & Time
End Sub

If "reading" means last selection of cell for looking its value then :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then ActiveSheet.Cells(1, 2) = Target.Address & " at " & Date & "/" & Time
End Sub


Hope this helps

 

[Roc]

Sorry for delay in replying. I'm trying these ideas out as we speak. Takes time as it requires realtime updates.
Range is filled in order down rows A1:A3000, cells blank until data input. Most recent data will always be at bottom of current range and all rows above most recent data will have text returned. (e.g. with update every second, row 1 will return first data, row 10 returns data after 10 secs etc). B1 needs to return current data and may change every second. I am having prob with blanks at bottom of range (not yet filled) being returned.