Opening of excel file takes to much time

[Natit]

Hello,
Every time I open (or change something in) this excel files' it takes about 12-13 sec.
Usualy I have anothe sheet like sht1, so it takes about 30 sec.
I don't think that the formulas in it are that "heavy".
Am I doing something wrong?
BTW "base" sheet is only 8 columns and 92 rows (I don't know how to add another sheet here)
Thx
Nati

question_file.xlsm
	A	B	C	D	E	F	G	H	I	J
1	year	code1	code2	data1	data2	data3	data4	data5	PCT	calc
2	2024	500161000	900016400	-	5,509,176	1,301,050	324,061	158,384	32%	3,725,681
3	2024	500161000	900246400	-	425,000	200,000	29,016	3,703	55%	192,281
4	2024	180060000	900016405	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
5	2024	500174000	900126400	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
6	2024	500174000	900176400	-	575,000	549,999	-	-	96%	25,001
7	2024	180060000	900016401	-	100,000	-	556	5,654	6%	93,790
8	2024	180060000	900016402	-	40,000	-	-	-	0%	40,000
9	2024	180060000	900016404	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
10	2024	180060000	900186400	-	20,000	-	-	-	0%	20,000
11	2024	500162000	900016400	-	100,000	-	-	-	0%	100,000
12	2024	500162000	900256400	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
13	2024	500165000	900216400	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
14	2024	500163000	900006400	-	636,167	-	62,934	59,669	19%	513,564
sht1

Cell Formulas
Range	Formula
D2:H14	D2	=LET( Col_num,MATCH(D$1,base!$1:$1,0), Col_char,SUBSTITUTE(ADDRESS(1,Col_num,4),"1",""), Yyear,base!$A:$A=2024, Ccode1,base!$B:$B=$B2, Ccode2,base!$C:$C=$C2, XLOOKUP(1,(Yyear)*(Ccode1)*(Ccode2),INDIRECT("base!"&Col_char&":"&Col_char)))
I2:I14	I2	=SUM(F2:H2)/E2
J2:J14	J2	=E2-F2-G2-H2

 

[SanjayGMusafir]

Usualy I have anothe sheet like sht1, so it takes about 30 sec.

The reason is XLOOKUP, INDIRECT and MATCH functions

They all tend to slow down excel

 

[Natit]

 

[Fluff]

Using whole column references, especially when doing array calculations, is a bad idea. As the Base sheet is only 92 rows limit the ranges to about 200 rows.

 

[Fluff]

Another option without the pointless volatile functions

=FILTER(INDEX(Base!$A$1:$Z$200,,XMATCH(D$1,Base!$A$1:$Z$1)),(Base!$A$1:$A$200=$A2)*(Base!$B$1:$B$200=$B2)*(Base!$C$1:$C$200=$C2))

 

[Natit]

The other option works perfect !!

 

[Fluff]

You're welcome & thanks for the feedback.