Excel lists
 

As I add daily numerical data to a list, I want the latest entry to appear in
an absolute reference cell. An example would be: I enter data in a range
(A1:A1000) and say I enter data in A260. I want the latest entry to appear in
cell $E$6, then the next day, data is entered in cell A261 and I want the
data in A261 to now appear in cell $E$6. How do I write this formula. Thank
you

Excel lists
 

put in E6 and press CTRL+SHIFT+ENTER

=INDIRECT("A"&ROW(INDEX(A:A,LARGE(IF(A1:A1000<"", ROW(A1:A1000)),1),)))

Excel lists
 

Hello Michael,

Assuming your lateset entry is the last one in Column "A", this code
will place the latest entry into $E$6 on the worksheet named Sheet1.
Change Sheet1 to the worksheet name you are using.


Code:
--------------------

Sub PostLastEntry()
Dim Addx
With Worksheets("Sheet1")
Addx = .Cells(.Rows.Count, "A").End(xlUp).Address
If Addx = "$A$1" And .Range("$A$1").Value = "" Then
MsgBox "There are no entries in the list."
Exit Sub
Else
.Range("$E$6").Value = .Range(Addx).Value
End If
End With
End Sub

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=571117