Automatic updating of formulas in excel predating the bible
This is of course because a real value is entered/changes - where the formula stays the same, so to speak.Question: Is it possible to create a VBA code (or something else) that updates D2 when the result of the formula in C2 changes?If possible, I need this to be active for the cells C2: C30 ( D2: D30 for the date time) Using Excel 2010.You could fill the dependend cell (D2) by a User Defined Function (VBA Macro Function) that takes the value of the C2-Cell as input parameter, returning the current date as ouput.Address) Set R2 = Range("C2: C20") Set Inter Sect Range = Application.Intersect(R1, R2) In Range = Not Inter Sect Range Is Nothing Set Inter Sect Range = Nothing If In Range = True Then R1. Value = Now() End If Set R1 = Nothing Set R2 = Nothing End Sub This only work if you manually change the data within the range (and it's perfect for that). and change the format of the column to the required date and time format.
For example, inputting the latest exchange rate for USD-GBP on the "rates" page will automatically update cells on other pages that use that rate within their formula.To make D2 only reflect the last time C2 was changed there would have to be some kind of tracking of the past value(s) of C2.This could for example be implemented in the UDF by providing also the address alonside the value of the input parameter, storing the input parameters in a hidden sheet, and comparing them with the previous values everytime the UDF gets called.This has an advantage of not changing the dates unless the content of the cell is changed, and it is in the range C2: C2, even if the sheet is closed and saved, it doesn't recalculate unless the adjacent cell changes.Adapted from this tip and @Paul S answer Private Sub Worksheet_Change(By Val Target As Range) Dim R1 As Range Dim R2 As Range Dim In Range As Boolean Set R1 = Range(Target.
I have tried a lot of VBA codes and tricks and none of them works if a formula is entered in C2.