CODEWITHSUNDEEP
timestampexcel-formula
RealHandy
RealHandy

Reputation: 602

Excel - update a cell value when another cell changes without VBA

I want to set a timestamp in a cell when the user changes another cell, but without VBA, as I want this to work in macro-less situations. All solutions I've found use Worksheet_Change VBA code, so I need an answer without VBA.

Example: The user updates column A. I want column B to record the timestamp when the user changes column A.

Upvotes: 1

Views: 13660

Answers (1)

RealHandy
RealHandy

Reputation: 602

This is a solution to the problem. It requires a trick, in that you have to turn on iterative calculations, because this approach takes advantage of circular references. So, this will only work in a workbook that otherwise doesn't need iterative calculations.

  1. Turn on iterative calculations, but set the max iterations to 1.

  2. In column B of each row, where the timestamp will go, use this formula. So for row 2, cell B2 will contain: =IF(AND(A2<>"",C2<>A2),NOW(),IF(A2="","n/a",B2))

  3. In column C (or any other unused column), put this formula. So for row 2, cell C2 will contain: =IF(A2="","",IF(OR(B2="",AND(ISNUMBER(C2),A2=C2)),C2,A2))

That's it. The result will look like this. You can format the timestamp to other values of course:

| A B C | 1| User Entry Timestamp Prev | 2| dog 6:28:55 PM dog | 3| cat 7:17:42 PM cat | 4| n/a |

The C column stores the value of A so that when A is changed, the formulas know that A has changed values. The timestamp won't change if the user enters the 'dog' cell but leaves 'dog' as the value. And the timestamp of row 2 doesn't change when you change cell A in some other row, which is a problem of some excel solutions that capture timestamps.

Upvotes: 3

Related Questions