Time stamp adjacent cell when formula changes given cell in range

[Mister Metzger]

Hi all,

Long time viewer, first time poster, haha. I can't even begin to say how much the posts here have helped me out at work. What I'm trying to accomplish is that when the value of a cell in column C changes, the cell adjacent to the changed cell is time-stamped with the time of the change. The VBA I'm currently using is:
<!--[if !supportLineBreakNewLine]-->

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Not Intersect(Target, Range("C:C")) Is Nothing Then 
        Target(1, 2) = Now()
        Target(1, 2).EntireColumn.AutoFit 
    End If 
End Sub

But, it will only add a time stamp if the change is manual and not as a result of a formula. I can't change the target to the source data because it changes every row and the sources are also formulas. The closest vba I've been able to find that might help with this is:

Private Sub Worksheet_Calculate()
Static oldval
If Range("C1").Value <> oldval Then
    oldval = Range("C1").Value
    '
    'rest of your code here
    '
End If
End Sub

But I don't know how to fully integrate this with my existing code, since the top one uses intersects and targets. Some additional information on my spreadsheet:

Columns A & B - Formulas referencing cells from various parts of the file. Each reference is different and the referenced cells are also formulas.
Column C - Formula to find the absolute difference between Columns A & B
Column D - Time stamp of the time a given cell in column C last changed

Thank you for any help you can provide. I hope this isn't too scattered. If there is anything else I can provide, please let me know.

Kind regards,


MM

 

[Mister Metzger]

What I was using originally was a function in column D:

Public Function Lastmodified(C As Range)

Lastmodified = Now()


End Function

However, it doesn't work 100% of the time. There was something tripping it up which would cause it to not recognize the cell changed. If it worked reliably, this would have been the easiest way to do what I want.

 

[skywriter]

How many cells are we actually talking about?

I think that when the file is first opened we can read the actual cells into a public array that will hold the values as they were when the workbook is opened.

Every time the calculate event for the worksheet is triggered we could create another array, compare it to the first array, change the cells that need changing and update the first array again.

This is just an idea in my head, maybe someone here might see a reason why that's not a good idea.

 

[Mister Metzger]

Hope you had a good weekend

We're looking at up to 40 cells per tab over 5 tabs. In turn, these cells go on to reference hundreds and hundreds of other cells. I had originally thought about moving the date stamp references to these source cells, but between their sheer number and that the final outcomes are variable (just because one input changes, doesn't mean the output changes), it's very impractical.

Your array idea is good! That's kind of what I had in mind with the second formula from the first post, but I don't know how to take it from a single cell "oldval" to an array "oldval" that would realize which line to time stamp.

 

[skywriter]

I would need something to work with for testing. A scaled down desensitized version of your workbook would be okay.

There are a several choices.

I know of drop box and google where you can upload a workbook that can be downloaded by someone else and I'm sure there are others.

 

[Mister Metzger]

Will do. Tomorrow's a holiday, but I'll get something whipped together for Monday.

 

[Mister Metzger]

I've created a really basic version for your use that doesn't contain any sensitive information. The actual file is far larger and more complex. You can find the link below. Thanks again for your help.

https://www.dropbox.com/s/y0yd3b2cco0y9uf/Time Changed Example.xlsm?dl=0

 

[Mister Metzger]

From an emailed set of questions, so others can still follow along:

Okay I've read back through posts and looked at you example spreadsheet and already I see problems.
1) In your earlier posts you talk about formulas in columns A and B and in your example I see no formulas.
2) Please do me a favor and understand that without a spreadsheet that's laid out exactly as your actual spreadsheet there will be a lot of time wasted.
3) You need to give me a lot of details because the things that will need to be known are things like which sheets if any need to be excluded, so details such as how many sheets will need to be excluded and how the code can identify those sheets. For example all the sheets the code should run against have the number 1 and a - after the 1. With a detail like that I can easily write code to just work on those sheets.
I need more detail and a very good example to work with. Please give me the best example sheet you can, with several examples, formatted the way it will actually be and as much detail as you can give me.

My responses:

Please see below to responses to each of your queries.

1) My earlier posts were conceptual only as I was trying to convey the general idea. When you asked for a spreadsheet, I gave you a better example. In relation to my earlier posts, columns A&B would be actually be C & E in the spreadsheet; column C per my post would be column G; and column D would be column H.
2) The spreadsheet on the link is a partial copy of the actual spreadsheet. I've just simplified it for practical purposes. For instance, instead of 40 error-checking lines, there are only two; instead of the formulas pulling from dozens of different worksheets and thousands of cells, they pull from a couple hundred cells on one worksheet. Everything functions the same, just much more condensed.
3) In the full workbook, there are 5 or so error checking sheets (which should be included) and say 50 other sheets which should be excluded (these are the sheets the error checking tabs pull from and would be the equivalent (actual content varies significantly sheet to sheet) of the "Random Data" tab in the supplied excel. The error checking sheets all start with "1. " (that's a number, period, and a space). I set this up because I have macros written that auto hide and unhide sheets based on which error sheet is selected and a given date.

Please feel free to ask me for any other detail you require. The excel in the link is the best representation I can make without taking a full day to create it. It is a good reflection of the original, just simpler and less content. Now that you have something to work with, please see below for how it's all supposed to work. I've also included notes in the spreadsheet itself.

Columns C & E pull various cell values from elsewhere in the workbook.
Column G compares the two and shows the difference.
Column H shows the time at which Column G changed. However, the custom "last modified" formula I'm currently using breaks when referenced cells contain certain formulas, like in the case of the formula in E16.
Cell J6 is the time the differences were accepted. This is updated whenever the "Accept differences" button is clicked.
Column I is a helper column that aids conditional formatting. If the difference in column G is greater than 10, it compares the time accepted with the time changed. If time accepted is greater than time changed, it returns a 1 (for yellow cell fill as a difference exists, but was accepted). If time accepted is less than time changed, it returns a 2 (for red fill as the difference changed after time of acceptance). Otherwise, it's returns a zero, for no issues.
Cell J7 is the time the spreadsheet was opened. I'd added this at the end because when the spreadsheet opens, everything recalculates which updates the time changed stamp. So, I was going to attempt to make the time changed stamp not update within 10 seconds of opening to avoid the recalculations.
Iterations won't work, as I need automatic calculations for other work in the file.

Please let me know if you need anything. Thanks again for your help