Record Number of counts to formula based cell - Excel

Sunil

Given your comment that Sheet 2 is updated remotely by a thridparty plug-in  (but you can't say how) I've tried to emulate a remote change by linking your Sheet2 to another .xlsx file (also attached) 

The attached .xlsm file takes a different approach to yours (and removes the VLOOKUP formula in the Strategy section, pasting values instead).

Open both files and you'll see that Sheet 2 cell E5 has the formula:

='[Remote data link v0_a.xlsx]Sheet1'!E4

and cells to the right and down have similar links.

With those links in place, if you change one of the yellow cells in the file Remote data link v0_a.xlsx, it triggers a worksheet calculation event for Sheet 2 of the .xlsm which runs this macro:

Private Sub Worksheet_Calculate()

'run through cells and modify Report if value has changed
For Each Cll In Sheet2.Range("Source")
    With Sheet1.Range(Cll.Address).Offset(-1, -2)
        If Cll.Value <> .Value Then
            .Value = Cll.Value
        End If
    End With
Next Cll

End Sub

This runs through the cells in Source range and changes any which aren't that value in renamed "Report sheet" (via a fixed offset).

That triggers this event macro which records a time and count of any changes to TRUE (provided they are between Start Time and End Time- you may need to chnage these if you;'re testing outside these hours):

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:E6")) Is Nothing Or Target.Value <> True Then Exit Sub
Debug.Print Target.Address
Dim HypTime As Date
Dim StartTime As Date, EndTime As Date

' get time period for changes
StartTime = Range("F1").Value
EndTime = Range("F2").Value
'set criteria met time
HypTime = Format(Now(), "hh:mm")

If (HypTime >= StartTime) And (HypTime <= EndTime) Then
    ' if True added between start and end times, add 1 to Number of Counts and record time
    Cells(Target.Row, 6) = Cells(Target.Row, 6) + 1
    Cells(Target.Row, 7) = HypTime
End If

End Sub

Hope this works for you (or you can modify it to suit).

Obviously, you are still in the designing stage, and that is a good thing because sheet design must occur in tandem with system design. So, I can't offer you a solution because your sheet design is not complete but the following can help you with the system design.

Key to your system is what you call "the period". During this time changes occur in Sheet2. These changes trigger what is called "change events", and these events trigger a count. All of this can't be done using formulas because formulas can't remember what was in a cell before and they also can't change a cell's value.

So, cell C4 of your "Formula based sheet" just displays the current calue of Sheet2!E5 while F4 shows how many times that result was achieved. G4 presumably shows the time when C4 was last True, but perhaps it refers to D4 and E4 as well.

As you see, the "Formula based sheet" is just a display. Ther action is on Sheet2, and it is very important how, exactly, the cells therre change their value.

When you deploy VBA code in your project it is beneficial to do so only after the sheet design is almost completely finished. In fact, while writing the code some changes may still have to be made. But if the code is written and you then want to add a column or add some more cells a lot of work will be involved. Therefore the approach should be to finalize the design and only then start writing the code.

For the moment you need to know that your target can't be reached using worksheet formulas. You will definitely need code.