Count only 1 time

[gtd526]

Hello,
Looking to perform a count, but when I hit 'save', it counts again.
How can I only count once, regardless of hitting 'save' (Q73).
Thank you.

NFL.xlsm
	L	M	N	O	P	Q	R
64	ESPN Pick w/ +ATS				Current W/L
65		%	SPRD	W/L	W	L	Psh
66	DAL	51%	2.5	W	1	0	0
67	CAR	51%	3	W	1	0	0
68					0	0	0
69					0	0	0
70						"0" Loses
71						1
72						ALL 0's
73						4
Weekly Picks

Cell Formulas
Range	Formula
L66	L66	=IFERROR(LOOKUP(2, 1/((COUNTIF($L$63:L63,$L$4:$L$35)=0)*($L$4:$L$35<>"")*($B$4:$B$35>0)),$L$4:$L$35),"")
M66:M69	M66	=IF(L66<>"",INDEX($M$4:$M$35,MATCH($L66,$L$4:$L$35,0)),"")
N66:N69	N66	=IF(L66<>"",INDEX($B$4:$B$35,MATCH($L66,$L$4:$L$35,0)),"")
O66:O69	O66	=IF(L66<>"",INDEX($D$4:$D$35,MATCH($L66,$A$4:$A$35,0)),"")
P66:P69	P66	=COUNTIFS($A$4:$A$35,$L66,$D$4:$D$35,"W")+R66*0.5
Q66:Q69	Q66	=COUNTIFS($A$4:$A$35,$L66,$D$4:$D$35,"L")+R66*0.5
R66:R69	R66	=COUNTIFS($A$4:$A$35,$L66,$D$4:$D$35,"Psh")
L67:L69	L67	=IFERROR(LOOKUP(2, 1/((COUNTIF($L$63:L66,$L$4:$L$35)=0)*($L$4:$L$35<>"")*($B$4:$B$35>0)),$L$4:$L$35),"")
Q71	Q71	=IF(AND(Q66=0,Q67=0,Q68=0,Q69=0),"1","0")
Q73	Q73	=Q71+Q73

 

[gtd526]

Hello,
Looking to perform a count, but when I hit 'save', it counts again.
How can I only count once, regardless of hitting 'save' (Q73).
Thank you.

NFL.xlsm
	L	M	N	O	P	Q	R
64	ESPN Pick w/ +ATS				Current W/L
65		%	SPRD	W/L	W	L	Psh
66	DAL	51%	2.5	W	1	0	0
67	CAR	51%	3	W	1	0	0
68					0	0	0
69					0	0	0
70						"0" Loses
71						1
72						ALL 0's
73						4
Weekly Picks

Cell Formulas
Range	Formula
L66	L66	=IFERROR(LOOKUP(2, 1/((COUNTIF($L$63:L63,$L$4:$L$35)=0)*($L$4:$L$35<>"")*($B$4:$B$35>0)),$L$4:$L$35),"")
M66:M69	M66	=IF(L66<>"",INDEX($M$4:$M$35,MATCH($L66,$L$4:$L$35,0)),"")
N66:N69	N66	=IF(L66<>"",INDEX($B$4:$B$35,MATCH($L66,$L$4:$L$35,0)),"")
O66:O69	O66	=IF(L66<>"",INDEX($D$4:$D$35,MATCH($L66,$A$4:$A$35,0)),"")
P66:P69	P66	=COUNTIFS($A$4:$A$35,$L66,$D$4:$D$35,"W")+R66*0.5
Q66:Q69	Q66	=COUNTIFS($A$4:$A$35,$L66,$D$4:$D$35,"L")+R66*0.5
R66:R69	R66	=COUNTIFS($A$4:$A$35,$L66,$D$4:$D$35,"Psh")
L67:L69	L67	=IFERROR(LOOKUP(2, 1/((COUNTIF($L$63:L66,$L$4:$L$35)=0)*($L$4:$L$35<>"")*($B$4:$B$35>0)),$L$4:$L$35),"")
Q71	Q71	=IF(AND(Q66=0,Q67=0,Q68=0,Q69=0),"1","0")
Q73	Q73	=Q71+Q73

These are Win-Loss columns (P,Q). When you have 0 losses for all teams, count as 1. Every time there are 0 losses, add 1 to that count.
The W-L record will change daily. I already have a cell formula to count when there are 0 losses (Q71).
You can use any cell or number of cell to complete the task. It could even be a macro that would run each night (as WL record will change daily).
Q73 is just a test and can be ignored (it keeps counting when I hit 'save').
Thank you,

 

[Joe4]

I think your circular reference is causing the issue. I sounds like it is running multiple times.
There is nothing that would make sure that it only updates the count exactly once a day.

If if were me, I think I would use VBA and add a column that tracks the date it was last updated.
So the macro would only run/update the count if that stored date was not today, and after updating the count, update the date to today.

But some details will need to be determined in order to build that VBA code, such as:
1. When exactly is the data updated daily? Is that manual or automatic? We obviously would not want to run the count code until the data has been updated.
2. How exactly should this VBA code be run? If someone going to be opening the file everyday and running it? Or is an automated solution necessary? If so, what should trigger the code to run?

There may be other questions that arise, depending on the answers.

 

[gtd526]

I think your circular reference is causing the issue. I sounds like it is running multiple times.
There is nothing that would make sure that it only updates the count exactly once a day.

If if were me, I think I would use VBA and add a column that tracks the date it was last updated.
So the macro would only run/update the count if that stored date was not today, and after updating the count, update the date to today.

But some details will need to be determined in order to build that VBA code, such as:
1. When exactly is the data updated daily? Is that manual or automatic? We obviously would not want to run the count code until the data has been updated.
2. How exactly should this VBA code be run? If someone going to be opening the file everyday and running it? Or is an automated solution necessary? If so, what should trigger the code to run?

There may be other questions that arise, depending on the answers.

I found a macro that solves the problem.

Dim d As Double
   
    d = Range("Q71").Value
    Range("Q73") = d + Range("Q73").Value

Thank you.

 

[Joe4]

Note that can be simplified to:

    Range("Q73") = Range("Q71").Value + Range("Q73").Value

(no need to introduce variables when just adding two static cells together).

However, this still involves someone having to manually run the code, right?
And how do you ensure that it is only run once a day?