Record Number of counts to formula based cell - Excel


Hi Team,

I am looking for the solution for record number of counts to formula based cell.

Please advise for solution to record the number of counts for word "True" in given time period. 

For example, If "True" appear after fulfilling the criteria for 1st time than count to be 1.

Than again later during the time period, if criteria fulfills for 2nd time and the word "True" appear than the count to be 2. 

Than again further later during the time period, if again criteria fulfills for 3rd time and the word "True" appear than the count to be 3 and so on.

Secondly, please advise for formula for the cell fulfills 1st time criteria in excel.

I have attached the excel sheet for your reference.

Trust you understand the requirement. Let me know for more understanding.

Request you to please help for the solution, if possible.






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.



This seems remarkably like a question/ file posted before under the name of NitilA.
As Variatus says, code is needed here, a formula won't work. Once again however we don't know how the successive Trues are initiated. 
John_Ru (rep: 1072) Apr 14, '21 at 11:51 am
John, In which way does your comment contribute to the solution offered here?
Variatus (rep: 4549) Apr 14, '21 at 7:56 pm
@Variatus- the comment doesn't add to your answer, sorry (but does ask the question about the updating of the data).

I have however now added a possible solution for the user.

My earlier comment perhaps just shows that I was awake to the same misspellings from files in earlier questions (e.g. "Hypotethical" sic) plus the fact that the current question file Info shows Author: Nitil and Last modified by:Nitil.  Accordingly I'll add the pointer that the user can always use Excel menu File/Info/Inspect Workbook to remove such personal information from files.
John_Ru (rep: 1072) Apr 15, '21 at 4:37 am
Add to Discussion


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).


Hi John,
Thanks for your efforts. You almost understand my requirement. The same is i am looking for, but only the thing is i am looking for the result when "true" appear in sheet 1 which is formula based excel sheet. Because sheet 2 is which is not in my control, as the data is coming from third party excel plugin. 
Hope you understand my query and request you to please help with the solution.
SunilA May 6, '21 at 2:38 am

I don't know anything about how your "third party excel plugin" works but have you tried using the Worksheet event for SheetCalculate or the Workbook events for  BeforeRemoteChange, AfterRemoteChange, BeforeXmlImport or AfterXmlImport?
John_Ru (rep: 1072) May 6, '21 at 4:43 am
Hi John,
Sorry, I don't know anything about which you mention last. 
Please guide me through.
SunilA May 6, '21 at 5:30 am

I've changed my answer to show a remote change. If this doesn't work, sorry but I have no more time to work on this.
John_Ru (rep: 1072) May 6, '21 at 12:17 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login