1

I have created and excel spreed sheet. Its pulls External data from a website into sheet 1. On Sheet 2 Is where all my calculations are done.

In Sheet 2.. B1 is my Current Value that updates every hour, M1 Is my current Time, F1 Is my Current Time, A4:A27 Is my Date Range, B3:Y3 Is my Time Range, And I'm using an if statement.=if(AND(F1=A4:A27), (M1=(B3:y3),B1,"") If statement works fine. See image Below. image1 You can see on the 20-11-2017 there is a value under the 7 on today date. When the time changes to 8 the 7 value disappears. As see in the second image below. Because of the if statement not being true on the 7 value any longer. image2 I'm looking to store the history of the passed values. How can i allow the if statement to save the values as a value instead of a reference that keeps changing.

Community
  • 1
  • 1
Kenneth O'Donnell
  • 93
  • 11

2 Answers2

1

You can use the Worksheet_Change Event with the following code. Basically it checks for the cell which is changed, if the cells changed is the "Current Value" cell then it will update the related date / time cell in the table.

Just double check your cell references in code below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'If the changed cell is the Current Value cell
    If Target.Address = "$B$1" Then

        Dim LastRow As Long
        Dim DateRange As Range
        Dim TimeRange As Range

        'Can change the sheet name to what ever your final sheet will be called
        With Target.Worksheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            Set TimeRange = .Range("B3:Y3").Find(Hour(.Range("K1")))
            Set DateRange = .Range("A4:A" & LastRow).Find(.Range("F1"))
            .Cells(DateRange.Row, TimeRange.Column).Value = .Range("$B$1").Value
        End With

    End If

End Sub
Jean-Pierre Oosthuizen
  • 2,653
  • 2
  • 10
  • 34
  • Jean-Pierre thanks really so much, your code works when i input the value manual in B1. Goes to the correct place. Just having a issue where the data chances automatically but does not copy the values. Only if i change it manually. Any advise on this? – Kenneth O'Donnell Dec 26 '17 at 11:22
  • @KennethO'Donnell, how do you mean automatically? Do you mean if there is a formula in the cell and then the value of the cell changes automatically ? – Jean-Pierre Oosthuizen Jan 02 '18 at 06:10
  • Thanks so much Jean-Pierre. I just made a copy and paste function to run every 10 min for it updates the cell which allowed the cell to change. Your code works perfectly. Thanks so much once again. – Kenneth O'Donnell Jan 19 '18 at 07:33
  • @KennethO'Donnell absolute pleasure Sir – Jean-Pierre Oosthuizen Jan 22 '18 at 09:06
0

What you are looking for is TrackChanges. Excel has a native TrackChanges functions, that when needed, can export the history like you wanted, to a seperated sheet.

The downside is, however, you have to share it. More information can be found here:

Track changes in a Shared Workbook

Important: This article explains an older method of tracking changes using a "Shared Workbook." The Shared Workbook feature has many limitations and has been replaced by co-authoring. Co-authoring doesn't provide the ability to track changes. However, if you and others have the file open at the same time, you can see each other's selections and changes as they happen. Also, if the file is stored on the cloud, it's possible to view past versions so you can see each person's changes. Learn more about co-authoring.

If you want to go with VBA route, you can make a sub to copy every newly data added to the worksheet whenever it changes ( event-trigger sub )

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Do stuff when worksheet changes
End Sub

An example is this: How do I get the old value of a changed cell in Excel VBA?

Shirayuki
  • 188
  • 1
  • 7