Last modified date for a range of cells

[DJPowelly]

Hi, I need a bit of help please.

I have a table that tracks learner progress. I would like to be able to show in a cell the last date any cell in a range was modified.
I have set this up as a table to allow others to add learners and not have to copy and past all of the formulas each time, so each time a new row is added the date has to relate to the cells in that row.

I would like to look at cells P4 to X4 and have the latest date that any of those cells were modified in Y4. When a new learner is added i would need Y5 to record the date from P5 to X5 and so on as new rows are added.

An alternative might be to take the date from O4 as this is calculated from cells P4 to X4 and should update whenever on of those cells changes.

I have looked at other VBA codes and they apply to the whole sheet or workbook and I would like to look at specific cells.

Any help would be greatly appreciated.

Many thanks

Keith

 

[rollis13]

To be of any other help I need a dummy copy of your file, just need the first few rows per sheet and of the table where manual entry takes place.

 

[DJPowelly]

I really appreciate your help with this.
How do I get the file to you?
I cannot see a way of attaching it in a message.

 

[rollis13]

I would suggest using File Hosting to upload your dummy file and then attach link here; easy, secure and no registration: LINK

 

[DJPowelly]

I would suggest using File Hosting to upload your dummy file and then attach link here; easy, secure and no registration: LINK

Great, here is the link http://www.wikifortio.com/828248/Candidate Tracker V2 - example.xlsm

A brief explanation of what the spreadsheet is doing.
As the learners complete assessments the log number is manually entered into the relevant cells in the 1605 tab.
The various completion percentages at the end of each section are where the xlookups get the values that are shown on the Progress_overview tab.

I have entered your code onto the 1605 sheet and it shows the value in cell AEK and is currently looking at cell AEA for the manual change. this works great for that cell and the plan is to then have a lookup to show the information on the progress_overview sheet.

What I would like it to do is long along the whole row in the 1605 sheet and show the last date modified in column AEK for that row.

There are some hidden cells on 1605 that are doing calculations for the amount of evidence needed for each section. They are not manually changed so I am not sure if it is easier to have a range from column c to AEJ.

I really do appreciate the time you are putting into this.

 

[rollis13]

So, if I correctly understood:
1) the name found in column B of sheet '1605' will be unique and found the same in column N of sheet 'Progress_Overview'.
2) yes, changes in range from column C to AEJ of sheet '1605' should be okay to time-stamp column Y in sheet 'Progress_Overview'.
Please test this Worksheet_Change event to be pasted in your '1605' sheet's module. Please temporally deactivate the other Worksheet_Change event already present. If my new macro is in working condition then these two events need to be merged.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim LastName As Long
    Dim RowName As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    If Target.Cells.CountLarge > 1 Or Intersect(Target, Range("C7:AEJ" & LastRow)) Is Nothing Then Exit Sub
    With Sheets("Progress_Overview")
        LastName = .Range("N" & Rows.Count).End(xlUp).Row
        'On Error GoTo Xit
        'RowName = .Range("A4:A" & LastName).Find(Range("A" & Target.Row), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        RowName = .Range("N4:N" & LastName).Find(Range("B" & Target.Row), , , xlByRows, xlPrevious).Row
        .Range("Y" & RowName) = Format(Now, "dd/mm/yyyy - hh:mm:ss")
        Exit Sub
    End With
Xit:
    MsgBox "Name " & Range("B" & Target.Row) & " not found in sheet Progress_Overview"
End Sub

 

[DJPowelly]

So, if I correctly understood:
1) the name found in column B of sheet '1605' will be unique and found the same in column N of sheet 'Progress_Overview'.
2) yes, changes in range from column C to AEJ of sheet '1605' should be okay to time-stamp column Y in sheet 'Progress_Overview'.
Please test this Worksheet_Change event to be pasted in your '1605' sheet's module. Please temporally deactivate the other Worksheet_Change event already present. If my new macro is in working condition then these two events need to be merged.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim LastName As Long
    Dim RowName As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    If Target.Cells.CountLarge > 1 Or Intersect(Target, Range("C7:AEJ" & LastRow)) Is Nothing Then Exit Sub
    With Sheets("Progress_Overview")
        LastName = .Range("N" & Rows.Count).End(xlUp).Row
        'On Error GoTo Xit
        'RowName = .Range("A4:A" & LastName).Find(Range("A" & Target.Row), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        RowName = .Range("N4:N" & LastName).Find(Range("B" & Target.Row), , , xlByRows, xlPrevious).Row
        .Range("Y" & RowName) = Format(Now, "dd/mm/yyyy - hh:mm:ss")
        Exit Sub
    End With
Xit:
    MsgBox "Name " & Range("B" & Target.Row) & " not found in sheet Progress_Overview"
End Sub

You are an absolute legend. That works fine, and automatically updates in the progress area without me having to add a lookup.
I cannot thank you enough.

 

[rollis13]

So, if that works here is the merged version to time-stamp column Y (sheet Progress_Overview) and column AEK (sheet 1605) at the same time:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim LastName As Long
    Dim RowName As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row 'last row in sheet 1605
    If Intersect(Target, Range("C7:AEJ" & LastRow)) Is Nothing Then Exit Sub 'out of range or multicell selection
    With Sheets("Progress_Overview")
        LastName = .Range("N" & Rows.Count).End(xlUp).Row 'last name in sheet Progress_Overview
        On Error GoTo Xit
        RowName = .Range("N4:N" & LastName).Find(Range("B" & Target.Row), , , xlByRows, xlPrevious).Row 'search name in sheet Progress_Overview, detect row
        .Range("Y" & RowName) = Format(Now, "dd/mm/yyyy - hh:mm:ss") 'date-stamp sheet Progress_Overview
        Range("AEK" & Target.Row) = Format(Now, "mm/dd/yyyy") 'date-stamp sheet 1605 <- added
        Exit Sub
    End With
Xit:
    MsgBox "Name " & Range("B" & Target.Row) & " not found in sheet Progress_Overview"
End Sub

 

[DJPowelly]

So, if that works here is the merged version to time-stamp column Y (sheet Progress_Overview) and column AEK (sheet 1605) at the same time:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim LastName As Long
    Dim RowName As Long
    LastRow = Range("B" & Rows.Count).End(xlUp).Row 'last row in sheet 1605
    If Intersect(Target, Range("C7:AEJ" & LastRow)) Is Nothing Then Exit Sub 'out of range or multicell selection
    With Sheets("Progress_Overview")
        LastName = .Range("N" & Rows.Count).End(xlUp).Row 'last name in sheet Progress_Overview
        On Error GoTo Xit
        RowName = .Range("N4:N" & LastName).Find(Range("B" & Target.Row), , , xlByRows, xlPrevious).Row 'search name in sheet Progress_Overview, detect row
        .Range("Y" & RowName) = Format(Now, "dd/mm/yyyy - hh:mm:ss") 'date-stamp sheet Progress_Overview
        Range("AEK" & Target.Row) = Format(Now, "mm/dd/yyyy") 'date-stamp sheet 1605 <- added
        Exit Sub
    End With
Xit:
    MsgBox "Name " & Range("B" & Target.Row) & " not found in sheet Progress_Overview"
End Sub

Thank you so much, you have been a real help with this.
I really appreciate your time and effort.

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.