Calculating causes formula results to disappear from all sheets

[Gregory123987]

When I save the workbook excel triggers a calculation of the workbook. I have no problem with this and I can always turn it off.

When I go to each sheet and calculate the sheet the results remain visible on each sheet.
When a calculation is done on this workbook, the formula results disappear from each sheet. Specifically, the formulas are still there but the calculated results are not even after the calculation is complete. Is there some reason this happens, and can it be prevented?

Edit: Additional information: 200,000+ formulas on 12 tabs.

 

[Alex Blakenburg]

Hard to trouble shoot without access to the workbook.
Is the workbook an xlsx file
OR xlsm / xlsb
If the latter do you have macros in the workbook and are any of them workbook events ?

Have you used VBA and know how to use the immediate window ?
If yes, then when you do a full calculate and the results don't show, what happens if you go to the immediate window and type or copy paste in the below and then hit enter ?
Application.Screenupdating = TRUE
Does anything change ?

 

[Gregory123987]

Hard to trouble shoot without access to the workbook.
Is the workbook an xlsx file
OR xlsm / xlsb
If the latter do you have macros in the workbook and are any of them workbook events ?

Have you used VBA and know how to use the immediate window ?
If yes, then when you do a full calculate and the results don't show, what happens if you go to the immediate window and type or copy paste in the below and then hit enter ?
Application.Screenupdating = TRUE
Does anything change ?

It is an xlsm file.
There are no workbook events. All UDFs are in Module1.

I entered the command both before and after calculating and nothing happens.

FYI, I use the buttons on the formulas tab Calculate Now and also Calculate Sheet. When I click Calculate Sheet for each worksheet "Jan" through "Dec" and all the sheets work and display as they should.

I can send you the workbook itself if you provide me a way to do it.

 

[Gregory123987]

If it makes a difference, I have multiple formulas, UDF to check the color in ranges, standard formulas, and complex conditional formulas as well.

 

[Joe4]

Edit: Additional information: 200,000+ formulas on 12 tabs.

When I see something like this, the first thing that pops in my head is "you may be using the wrong tool for the job".
Are a lot of your formulas VLOOKUP or INDEX/MATCH equations, linking data across worksheets?

If so, what you may really have is a Relational Database, in which case using a Relational Database program like Microsoft Access or SQL is much more efficient, as that is that those programs were designed for.

Note that Excel does have a limit regarding how many formula dependencies it can track (I think it is something like 1 million dependencies). After that, it takes a toll on performance, where any time any update is made, it recalculates everything (which is very SLOW). I am guessing maybe that is why you are messing with the Calculation settings. I don't know if this also has anything to do with the weird behavior you are seeing. But if I were you, I would probably strongly consider using another program for this project.

 

[Gregory123987]

When I see something like this, the first thing that pops in my head is "you may be using the wrong tool for the job".
Are a lot of your formulas VLOOKUP or INDEX/MATCH equations, linking data across worksheets?

Not a true database format. There are none of the db formulas. However, the data does link to the next tab, i.e., each month links to the next month.

If so, what you may really have is a Relational Database, in which case using a Relational Database program like Microsoft Access or SQL is much more efficient, as that is that those programs were designed for.

Note that Excel does have a limit regarding how many formula dependencies it can track (I think it is something like 1 million dependencies). After that, it takes a toll on performance, where any time any update is made, it recalculates everything (which is very SLOW). I am guessing maybe that is why you are messing with the Calculation settings. I don't know if this also has anything to do with the weird behavior you are seeing. But if I were you, I would probably strongly consider using another program for this project.

Not a true database. I have already suggested converting to a database application as their needs / wants are changing / increasing.

I intend to instruct the user to operate in manual mode for entering data, then switching to automatic mode for calculating, and to calculate the worksheets sequentially.

Below is a sample of the worksheet with 5 rows of data, which includes a sample of all the formulas. There are a total of 500 rows for data for each worksheet. The attached image show the Workbook Statistics for the worksheet "Dec". The sheet stats can be considered average for each sheet.

S2F Data Laps 20210922 Demo.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ
9
10	December 2021
11	Member Data						Prior Month Totals						Totals						Totals								# Participants per Day Totals
12							0	0	0	0	1849	1235	0	0	0	0	1849	1235	0	0	0						0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	ACTIVE This Month	New Member This Month	Name		DOB Used for Statistical Reporting	Ethnicity Used for Statistical Reporting	PRIOR Month						This Month				YEAR TO DATE		Other Earned Laps								LAPS RUN
14																											W	TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F		Preview					Incentives 'earned' this month					Incentives Total of Previous month (YTD) Link to Prev Month Total					Determines Incentives earned this Month ONLY IF Previous Month YTD is 0					YTD Total of Previous and Current Month (max of 1) for link to next month
16			First	Last			Total Laps	Total Miles	Total # Days	Consecutive Days	Total Miles YTD	Total Days YTD	Total Laps Run	Total Miles Run	# of Days	Consecutive Days	Total Miles	Total Days	Races (Laps)	Volunteer (hours)	Yoga (laps)	Sundays
17																											1	2	3	4	6	7	8	9	10	11	13	14	15	16	17	18	#	21	#	#	#	#	#	#	#	#	31
18
19		Y	Sean	Rankin	2/10/1980	W	0	0	0	0	11	7	0	0	0	0	11	7																																										0	0	0	0		1	1	0	0		0	0	0	0		1	1	0	0
20		Y	Jesse	Highum	3/5/1986	W	0	0	0	0	55	34	0	0	0	0	55	34																																										0	0	0	0		1	1	1	1		0	0	0	0		1	1	1	1
21		Y	Analisa	Bocanegra	2/16/2000	H	0	0	0	0	24	20	0	0	0	0	24	20																																										0	0	0	0		1	1	1	1		0	0	0	0		1	1	1	1
22		Y	Ruben	Gaytan	2/1/1983	H	0	0	0	0	52	36	0	0	0	0	52	36																																										0	0	0	0		1	1	1	1		0	0	0	0		1	1	1	1
23		Y	Andres	Deleon	12/17/1983	H	0	0	0	0	95	64	0	0	0	0	95	64																																										0	0	0	0		1	1	1	1		0	0	0	0		1	1	1	1
Dec

Cell Formulas
Range	Formula
G12:U12	G12	=SUM(G19:G518)
AA12:BA12	AA12	=COUNT(AA19:AA518)
S16:U16	S16	=Nov!S16
A19:A23	A19	=IF(SUM(H19,AA19:BA19)>0,"Y","")
B19:B23	B19	=IF(C19<>"","Y","")
C19:F23	C19	=IF(Nov!C19<>"",Nov!C19,"")
G19:L23	G19	=Nov!M19
M19:M23	M19	=SUM($AA19:$BA19)
N19	N19	=$M19*0.6213
O19	O19	=COUNT($AA19:$BA19)
P19:P23	P19	=IF(M19<>0,($BA$15-(MAX(($AA19:$BA19="")*($AA$15:$BA$15)))),0)
Q19:Q23	Q19	=+N19+K19
R19:R23	R19	=SUM(L19+O19)
N20:N23	N20	=M20*0.6213
O20:O23	O20	=COUNT(AA20:BA20)
BH19:BH23	BH19	=DFColor($AA19:$BA19,255)
BI19:BI23	BI19	=DFColor($AA19:$BA19,65535)
BJ19:BJ23	BJ19	=DFColor($AA19:$BA19,49407)
BK19:BK23	BK19	=DFColor($AA19:$BA19,15773696)
BM19:BP23	BM19	=Nov!BV19
BR19:BU23	BR19	=IF(BM19=0,IF(BH19>0,1,0),0)
BW19:BZ23	BW19	=IF(BM19+BR19>0,1,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BR19:BU518	Cell Value	>0	text	NO
BH19:BQ518,BV19:BZ518	Cell Value	>0	text	NO
BF19:BF518	Expression	=BZ19>0	text	NO
BE19:BE518	Expression	=BY19>0	text	NO
BD19:BD518	Expression	=BX19>0	text	NO
BC19:BC518	Expression	=BW19>0	text	NO
BF17	Cell	contains a blank value	text	NO
BE17	Cell	contains a blank value	text	NO
BD17	Cell	contains a blank value	text	NO
BC17	Cell	contains a blank value	text	NO
BZ17	Cell	contains a blank value	text	NO
BY17	Cell	contains a blank value	text	NO
BX17	Cell	contains a blank value	text	NO
BW17	Cell	contains a blank value	text	NO
BU17	Cell	contains a blank value	text	NO
BT17	Cell	contains a blank value	text	NO
BS17	Cell	contains a blank value	text	NO
BR17	Cell	contains a blank value	text	NO
BP17	Cell	contains a blank value	text	NO
BO17	Cell	contains a blank value	text	NO
BN17	Cell	contains a blank value	text	NO
BM17	Cell	contains a blank value	text	NO
BK17	Cell	contains a blank value	text	NO
BJ17	Cell	contains a blank value	text	NO
BI17	Cell	contains a blank value	text	NO
BH17	Cell	contains a blank value	text	NO
C19:D518	Expression	=ISFORMULA(C19)	text	NO
M19:M102	Cell Value	>=50	text	NO
AA19:BA518	Expression	=IF(AA19="","",IF(COUNTBLANK($AA19:AA19),IF(COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19)))>$J19+COUNT(OFFSET($AA19,,,,MATCH(TRUE,$AA19:AA19="",0))),COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19))),""),MOD(IF($J19<10,$J19)+COUNT($AA19:AA19)-1,IF(COLUMNS($AA19:AA19)<10,10,1E+100))+1))=10	text	NO
AA19:BA518	Expression	=IF(AA19="","",IF(COUNTBLANK($AA19:AA19),IF(COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19)))>$J19+COUNT(OFFSET($AA19,,,,MATCH(TRUE,$AA19:AA19="",0))),COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19))),""),MOD(IF($J19<10,$J19)+COUNT($AA19:AA19)-1,IF(COLUMNS($AA19:AA19)<10,10,1E+100))+1))=5	text	NO
AA19:BA518	Expression	=IF(AA19="","",IF(COUNTBLANK($AA19:AA19),IF(COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19)))>$J19+COUNT(OFFSET($AA19,,,,MATCH(TRUE,$AA19:AA19="",0))),COUNT(OFFSET(AA19,,,,MATCH(2,1/($AA19:AA19=""))-COLUMNS($AA19:AA19))),""),MOD(IF($J19<10,$J19)+COUNT($AA19:AA19)-1,IF(COLUMNS($AA19:AA19)<10,10,1E+100))+1))=3	text	NO
AA19:BA518	Expression	=AND(AA19<>"",($J19+COUNT(AA19:$AA19))=1)	text	NO

 

[Alex Blakenburg]

Just for interest, what happens if you make a copy of the workbook and clear all the conditional formatting ?
Does the problem go away ?

 

[Gregory123987]

Just for interest, what happens if you make a copy of the workbook and clear all the conditional formatting ?
Does the problem go away ?

Most of the formulas rely on the conditional formatting. clearing all of them would be pointless. If you want, I can email you the workbook and you can try it.

 

[Joe4]

That certainly looks like a relational database to me, especially when you talk about data for different months.
If you find that most of these sheets are similar, and just "copies" of one another (i.e. for each month), you are probably actually dealing with a database structure.

 

[Gregory123987]

That certainly looks like a relational database to me, especially when you talk about data for different months.
If you find that most of these sheets are similar, and just "copies" of one another (i.e. for each month), you are probably actually dealing with a database structure.

Only forwarding client names / DOB / Ethnicity and accumulated totals to the next sheet. I have recommended to the client to convert to a relational database application.