Previous cell value

[bhsoundman]

Hi All,

I'm looking to return the value of of the last entry in a range. I've attached the sample here
My data range is C5:I15, and my return range is D2

Basically when I enter data into my data range, I want that data to appear in D2. So, D2 is always the value of the previous cell. Seems simple, but I keep running into issues.

Thanks!!

 

[jasonb75]

The correct answer depends on the data type and the order in which the range is filled. Assuming numeric data filled across then down, this formula will work.

=LOOKUP(1E+100,INDEX($C$5:$I$15,MATCH(1E+100,$C$5:$C$15),0))

 

[bhsoundman]

The correct answer depends on the data type and the order in which the range is filled. Assuming numeric data filled across then down, this formula will work.

=LOOKUP(1E+100,INDEX($C$5:$I$15,MATCH(1E+100,$C$5:$C$15),0))

Thanks for the formula. I may need to clarify that the data will be text & the answer that would return in D2 would be the text regardless of where it appears in the data range. Whatever is the most recent entry is what should be returned into D2.

Thanks!!

 

[jasonb75]

In that case you will need to use vba, a formula will not be able to track the order in which entries are made if there is no pattern to the entry.

Copy the code below and paste it into the relevant worksheet module by right clicking the sheet / tab name in excel and selecting 'View Code' from the context menu.
Close the code editor and save the workbook, if you see a pop up when you try to save then you will need to follow the instructions to save as macro enabled.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5:I15")) Is Nothing Then
    If Target.CountLarge = 1 Then
        If Target.Value <> "" Then Range("D2").Value = Target.Value
    End If
End If
End Sub

Hope this helps.

 

[bhsoundman]

In that case you will need to use vba, a formula will not be able to track the order in which entries are made if there is no pattern to the entry.

Copy the code below and paste it into the relevant worksheet module by right clicking the sheet / tab name in excel and selecting 'View Code' from the context menu.
Close the code editor and save the workbook, if you see a pop up when you try to save then you will need to follow the instructions to save as macro enabled.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C5:I15")) Is Nothing Then
    If Target.CountLarge = 1 Then
        If Target.Value <> "" Then Range("D2").Value = Target.Value
    End If
End If
End Sub

Hope this helps.

YOU RULE! This works exactly as needed! Thanks so much.