[Last Call] Learn how to a build a cloud-first strategyRegister Now


Prevent recalculation of NOW() function in Col B that depends on values entered in Col A.

Posted on 2011-04-30
Medium Priority
Last Modified: 2012-05-11
Values are scanned into cells in Col A and the adjacent cell in Col B has the following calculation.
CELL B2:    =If(A2="","",NOW())
CELL B3     =If(A3="","",NOW())
CELL B4     =If(A4="","",NOW())

The intial calc works OK for each entry in Col A, but the previous calc in Col B recalculates to the
current NOW() value.
I need to keep the original value calulated, for example, in Cell B2 that was calculated when a
value was entered into Cell A2.
Currently, the value in Cell B2 is recalculting when (ie, all previious calculated values in Col B
are recalulating) when subsequent values are scanned into sunsequent cells in Col A such as
Cell A3, Cell A$, etc.

Question by:amcgaughey1
LVL 81

Expert Comment

ID: 35499428
You would need to assign the value of NOW() to a variable prior to processing this.
is this an Excel VBA/Macro you are working on?

Author Comment

ID: 35499538
This is not an Excel VBA?Macro.  Input into cells in Col A are via barcode scan.  As each scan is processed the focus shifts to the next cell below.  Since the cell thta just received the input has a value it causes the contiguous in Col B to calculate witht he current date/time which is what is the desired datra capture.  However, all the other cells in Col B wiith the "IF" calculation also recalculate to the same date/time as that caused by the most recent data entry.  This is not what I need. I need to retain the date/time that is originallyncaltaed with its associated data entry in Col A.
If I am going to use an Excel VBA\Macro what event would I use to triiger the associated calaculation data capture?
LVL 71

Expert Comment

ID: 35500963
Just add another IF, checking whether there is already an value in the B cells:

B2: = If(isempty(A2),"", If(isempty(B2), NOW(), B2))
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

LVL 22

Accepted Solution

rspahitz earned 400 total points
ID: 35500965
In VBA, you'd probably need something that adds the current time when a particular cell changes.

Assuming you are on Sheet1, use Alt+F11 to get into the VBA area then double-click on Sheet1 in the project panel (top, left corner, may need Ctrl+R to open it.)
In the window that opens to the right, enter this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const TimeColumn As Integer = 2
    Const ValueColumn As Integer = 1
    Dim iRow As Integer
    If Target.Count = 1 Then
        iRow = Target.Row
        If Cells(iRow, TimeColumn).Value = "" And Cells(iRow, ValueColumn).Value <> "" Then
            Cells(iRow, TimeColumn).Value = Now
        End If
    End If
End Sub

Open in new window

Now every time a value changes (except when a value is cleared) the current time will be put in the time column.
You may need to adjust the column.  I inferred that the data is column 1 and the time is supposed to be in column 2.
You should also remove any formulas from the time column.

Expert Comment

ID: 35501028
If I recall, a barcode input won't trigger a Change event, but you can use a calculate event:

Private Sub Worksheet_Calculate()
    Dim i As Long
        i = Cells(Rows.Count, "A").End(xlUp).Row
        Cells(i, "B").Value = Now
End Sub

Open in new window


Author Closing Comment

ID: 35507622
Perfect. Thank you!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question