Formula Causes Memory Leak...Sometimes?!? Help me diagnose

[Tarkemelion]

Hi Team,

I have an Excel template which itemises a list of items. The numbering of each item is what makes this task particularly challenging. Depending on the column that the item is sitting in determines the numbering increment. A picture is worth 1000 words so I'll show the general gist of it below:

1		Sports
1.1			Ball Games
1.1.1				Soccer
1.1.2				Football
1.1.2.1					Touch
1.1.2.2					Gridiron
1.1.3				Basketball
1.2			Racquet Games
1.2.1				Tennis
1.2.2				Badminton
1.3			Other
1.3.1				Archery
2		Jobs
2.1			Medicine
2.1.1				GP
2.1.2				Surgeon
2.2			Law
2.2.1				Solicitor
2.2.2				Prosecutor
2.3			Engineering
2.3.1				Civil
2.3.2				Mechanical
2.3.3				Electrical

As you can see, the numbering scheme is a little complex. The formula adjacent to the "Sports" item is:

=IF(C2="",IFERROR(LET(d,".",c,XMATCH("*",C2:F2,2),p,A1&d&0,TEXTBEFORE(p,d,c-1)&d&(INDEX(TEXTSPLIT(p,d),c)+1)),0),COUNTA(C$2:C2))

This effectively searches for how many main headings are in Column C (i.e. Sports, Jobs etc.), and the rest of it tries to determine the step that needs to be expressed by looking at the number immediately above.

It seems effective and I'm happy with it.

To the problem: I noticed that my template was freezing when I added in new rows, particularly deep into the template (i.e. row 200-300+). By screen freeze, I mean that nothing on the screen would update. However, I could interact with ribbon buttons, such as the save icon, and could see the sheet execute after the supposed button press. Visually, the buttons would not react at all, but the excel sheet was working. Closing and reopening (after a save) would carry through the changes so the sheet was working, just not visually.
When I switched the sheet to manual calculations, added a new row and the corresponding data and then copied the formula into the numbering cell, everything was fine. As soon as I updated the sheet calculation (F9), it would freeze, leading me to believe that this is the trigger point of the problem.
I will note that there is also some conditional formatting associate with the row which formats each row depending on where the data is sitting (fairly simple things, like bold text with a light grey fill if it's a title, underlined text if it's a subtitle etc.).
Has anyone had this issue before, and know how to solve it? My formula doesn't use anything particularly volatile, so my expectation is that it isn't particularly calculation-heavy.

I want to avoid having to calculate things in a macro, but if I need to do that and then copy the results (value-only) into the numbering column to remove updating formulas from the actual sheet, then so be it.

Any and all thoughts are appreciated.

Cheers!

 

[Eric W]

I've definitely seen cases where many complicated functions cause the sheet to lock up. It's kind of hard to predict, there's no good way to determine what will do it. 300 rows really doesn't seem like that many. I remember working on this problem before. I played around again, and found a slightly shorter formula with a different mix of functions. Try this:

Book1
	A	B	C	D	E	F
1	1		Sports
2	1.1			Ball Games
3	1.1.1				Soccer
4	1.1.2				Football
5	1.1.2.1					Touch
6	1.1.2.2					Gridiron
7	1.1.3				Basketball
8	1.2			Racquet Games
9	1.2.1				Tennis
10	1.2.2				Badminton
11	1.3			Other
12	1.3.1				Archery
13	2		Jobs
14	2.1			Medicine
15	2.1.1				GP
16	2.1.2				Surgeon
17	2.2			Law
18	2.2.1				Solicitor
19	2.2.2				Prosecutor
20	2.3			Engineering
21	2.3.1				Civil
22	2.3.2				Mechanical
23	2.3.3				Electrical
Sheet3

Cell Formulas
Range	Formula
A2:A23	A2	=LET(a,textsplit(A1&".0.0.0","."),s,SEQUENCE(,COUNTA(a)),b,XMATCH("*",C2:F2,2),TEXTJOIN(".",1,IFS(s<b,a,s=b,a+1,1,"")))

Try this and see if anything changes. If not, I think you'll probably have to resort to VBA.

 

[myall_blues]

Is there other 'stuff' associated with this list - in cells to the right, for example?

I know this is an Excel page but that sort of numbering system is the sort that a properly set-up Word template would eat for breakfast.

 

[Tarkemelion]

Is there other 'stuff' associated with this list - in cells to the right, for example?

I know this is an Excel page but that sort of numbering system is the sort that a properly set-up Word template would eat for breakfast.

Agreed, but there are unit costs, quantities, and total costs associated with the template. Definitely needs to be a spreadsheet!

 

[Tarkemelion]

I've definitely seen cases where many complicated functions cause the sheet to lock up. It's kind of hard to predict, there's no good way to determine what will do it. 300 rows really doesn't seem like that many. I remember working on this problem before. I played around again, and found a slightly shorter formula with a different mix of functions. Try this:

Book1
	A	B	C	D	E	F
1	1		Sports
2	1.1			Ball Games
3	1.1.1				Soccer
4	1.1.2				Football
5	1.1.2.1					Touch
6	1.1.2.2					Gridiron
7	1.1.3				Basketball
8	1.2			Racquet Games
9	1.2.1				Tennis
10	1.2.2				Badminton
11	1.3			Other
12	1.3.1				Archery
13	2		Jobs
14	2.1			Medicine
15	2.1.1				GP
16	2.1.2				Surgeon
17	2.2			Law
18	2.2.1				Solicitor
19	2.2.2				Prosecutor
20	2.3			Engineering
21	2.3.1				Civil
22	2.3.2				Mechanical
23	2.3.3				Electrical
Sheet3

Cell Formulas
Range	Formula
A2:A23	A2	=LET(a,textsplit(A1&".0.0.0","."),s,SEQUENCE(,COUNTA(a)),b,XMATCH("*",C2:F2,2),TEXTJOIN(".",1,IFS(s<b,a,s=b,a+1,1,"")))

Try this and see if anything changes. If not, I think you'll probably have to resort to VBA.

Thanks mate, I've got a couple of other formulas that do the same to greater or lesser extents so I'll try those too and see if it avoids the issue. Otherwise I'll have a go at the VBA.

Appreciated.

 

[Tarkemelion]

It looks like this is a conditional formatting issue, now that I've spent more time investigating.

I'll mark the query as closed, and I appreciate the thoughts and feedback from everyone who took the time to review this issue. I'll open another query in the future to see if there is a work-around for having overlapping conditional formats and such.

Cheers all!