1 cell that reflects last entry made in any other cell

  • Thread starter Thread starter computerwolf
  • Start date Start date
C

computerwolf

I am using Excel 2003.

I have two columns in which I enter the weight of individual rolls of
plastic...first down column A, then down column B. I'd like to have one cell,
say in column C, that reflects the last entry I made, no matter where it is
in columns A or B. Can this be done?
 
so if column A is filled and column B has only a few entries, you want the
last entry from column B (ie - all of the entries in column b are considered
later than the entries in column a)?

if so, try:
=IF(COUNT(B:B),LOOKUP(MAX(B:B)+1,B:B),LOOKUP(MAX(A:A)+1,A:A))
 
Assuming you fill cells in column A first, then you start in column B:

=IF(COUNT(A:B),LOOKUP(1E100,IF(COUNT(B:B),B:B,A:A)),"")
 
Any cell in A or B in any order will require event code as far as I can tell.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:B1000" '<--------- change to suit
Dim cell As Range
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Range("C1").Value = Target.Value
End If
ws_exit:
Application.EnableEvents = True
End Sub

I put the last entry log cell as C1. You can Freeze Panes on row one so's C1 is
always visible.


Gord Dibben MS Excel MVP
 
Back
Top