Function for last modified date of cell.

[flpgdt]

Hi,

I was looking into this thread, which is pretty close to what I want, but still not quite. I kinda managed to do some modifications and turn it into a function but just don't know enough of these events handles.

What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.

E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"

-- Make an update:

A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"

I'm not quite a super user in Excel, but this function would be very very useful for me.
Could someone land me hand?

Thanks!

f.

ps. I'm using Office 2010

 

[svkroy]

Try the Worksheet change option in vba.

Private Sub Worksheet_Change(ByVal Target As Range)
'Your code here
End Sub

 

[flpgdt]

That's exactly what the other thread suggests... What I want is to turn that into a function....

Here's the code of the original thread:

Private Sub Worksheet_Change(byVal Target as Range)
If Target.Count > 1 Then exit Sub
If Target.Row < 2 Then Exit Sub
If Target.Column > 4 Then Exit Sub
Cells(Target.Row, 6) = Now
End Sub

 

[svkroy]

Public Function Lastmodified(c As Range)

Lastmodified = Now()

End Function

So, you should be typing "=lastmodified(A1)" in the cell

 

[flpgdt]

Oh.. that was easy. Had no idea. Thanks a lot

 

[TFH_Bane]

Ok , So I am new-ish to excel and I sort of understand the basics of formulas but when I do as described above I get the result of : "=lastmodified(A1)" >

#NAME?</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

What am I doing wrong here I looked at the code you put up there svkroy and I am not 100 % sure on where to enter that code. I am pretty sure thats what I am doing wrong.

 

[svkroy]

Ok , So I am new-ish to excel and I sort of understand the basics of formulas but when I do as described above I get the result of : "=lastmodified(A1)" >

#NAME?

<tbody>
</tbody>

What am I doing wrong here I looked at the code you put up there svkroy and I am not 100 % sure on where to enter that code. I am pretty sure thats what I am doing wrong.

The above code is an UDF (User Defined Function) google it for the basics, and copy paste the code to a module on your workbook.

1. Open Excel
2. Hit Alt+F11
3. Insert=>Module
4. Copy-Paste code there
5. Save, close, re-open workbook, then type in any cell =lastmodified(A1)
6. Works ?

 

[bigmyk2k]

Is there a way to modify this or the spreadsheet so that it can display a date/time earlier than when the formula is put in?

That is, I am using this in my spreadsheet (pretty nifty!) but I know that almost all of the data is old- as old as 2013. This probably won't work since I had to resave it as a .xlsm from .xlsx, but I'm trying to figure out what data I need to update and what I don't on an old sheet someone else created.

Also, this is a problem: if I insert a row in the sheet it updates every entry of this time (and, yes, the cell references are relative and correct). Even better, ctrl+z doesn't undo the change...

Thanks!

 

[Eric W]

The problem with that UDF is that it updates every time the sheet recalculates. And you have found the Ctrl-Z won't undo VBA changes. What it sounds like is that you're looking for a solution that only updates a date when a specific cell is changed. Try putting this code on the VBA sheet that you're changing:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C1:C100")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Now()
    
End Sub

This monitors your worksheet, and if any cell in the range C1:C100 is changed, then the date/time is stored in the column (D) next to it. The date will never change again, unless you change that cell again. And if you want, you can manually put in your own date in D.

Let me know if that's useful.

 

[bigmyk2k]

The problem with that UDF is that it updates every time the sheet recalculates. And you have found the Ctrl-Z won't undo VBA changes. What it sounds like is that you're looking for a solution that only updates a date when a specific cell is changed. Try putting this code on the VBA sheet that you're changing:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C1:C100")) Is Nothing Then Exit Sub
    Target.Offset(0, 1) = Now()
    
End Sub

This monitors your worksheet, and if any cell in the range C1:C100 is changed, then the date/time is stored in the column (D) next to it. The date will never change again, unless you change that cell again. And if you want, you can manually put in your own date in D.

Let me know if that's useful.

So, if ANY cell in that range changes, it updates the date?
So, if I want to know the date of the most recent update for each individual cell in that range (could be 100 different dates), I'd need to enter that macro 100X with a 1 cell range?
Basically, I am managing a really large price reference spreadsheet. I don't want to check everything, or every time, but I'd like to see (probably with conditional formatting) when a price hasn't been updated in, say, more than a year.
could just put a date column in, but that would mean that I'd need to basically start from scratch and update every one of the few thousand prices now (at about 10 minutes each) in order to know that I have an accurate starting price/date. If I have to, I have to. But I'd like to avoid that if I can.