Formula(s) Causing Excel to Crash Cannot Debug

[chrismichaelgentile]

Hello, I'm attempting to organizing an extraction file that was originally a tab delineated table (from AutoCAD ATTOUT). I copy this table to a sheet preset in my workbook (ATTOUT). I have three formulas: one that lists the symbols (column A), the other returns the quantity(ies) associated with the symbol (columns C:extent needed), and a sum that edits the text to a number (column B). The database from ACAD (the ATTOUT sheet can be quite extensive.

I usually work with the formula calculation method set to manual. I didn't realize this when I was writing the formulas, but Excel crashes when I try to recalculate. I cannot find where I went wrong, can someone please review my code?

X	A	B	C
1	SYMBOL	SUM	QUANTITIES
2	=IFERROR(INDEX(ATTOUT!$D:$D,MATCH(0,COUNTIF(ATTOUT!$D:$D,"<"&ATTOUT!$D:$D)-SUM(COUNTIF(ATTOUT!$D:$D,$A$1:A1)),0)),"")	=SUMPRODUCT(IFERROR(IFERROR(IF(ISTEXT(C3:CCC3),--LEFT(C3:CCC3, FIND("|",REPLACE(C3:CCC3,LEN(C3:CCC3)-2,LEN(C3:CCC3)-(LEN(C3:CCC3)-2), "|"))),C3:CCC3),IF(ISTEXT(C3:CCC3),--LEFT(C3:CCC3,FIND("|",REPLACE(C3:CCC3, LEN(C3:CCC3)-1,LEN(C3:CCC3)-(LEN(C3:CCC3)-1),"|"))),C3:CCC3)),C3:CCC3))	=IFERROR(INDEX(ATTOUT!$C:$C,SMALL(IF(ATTOUT!$D:$D=$A2, ROW(ATTOUT!$C:$C)-ROW(ATTOUT!$C$1)+1),COLUMNS($C:C))),"")
3	Ctrl+'	Ctrl+'	Ctrl+'

<tbody>
</tbody>

Formulas in A and B are copied down to extent needed. Formula in C is copied across to extent needed and down to match column A. Here is an example of the ATTOUT! sheet:

X	C	D
1	QTY	SYM
2	779 SF	CONC
3	1230 SF	REF
4	345 SF	DG

<tbody>
</tbody>

Thank you in advance!

 

[xenou]

Offhand it just looks like you have a lot of data (rows out to CC, entire columns referenced in your formula). Usually I just try to simplify in such cases so that there less going on in the formulas.
ξ

 

[chrismichaelgentile]

xenou, your comment actual helped! I turned the ranges into tables and used table nomenclature for dynamic ranges. This really helped.

I wonder if the formula in column C can be included within formula in column B...

Can Column C:

=IFERROR(INDEX(ATT[QTY],SMALL(IF(ATT[SYM]=$A2,ROW(ATT[QTY])-ROW(ATTOUT!$C$1)+1),COLUMNS($D2:D2))),"")

be combined within column B:

=SUMPRODUCT(IFERROR(IFERROR(IF(ISTEXT(DATA[#This Row]),--LEFT(DATA[#This Row],FIND("|",REPLACE(DATA[#This Row],LEN(DATA[#This Row])-2,LEN(DATA[#This Row])-(LEN(DATA[#This Row])-2),"|")))),IF(ISTEXT(DATA[#This Row]),--LEFT(DATA[#This Row],FIND("|",REPLACE(DATA[#This Row],LEN(DATA[#This Row])-1,LEN(DATA[#This Row])-(LEN(DATA[#This Row])-1),"|"))))),DATA[#This Row]))

The reference to DATA[#This Row] in column B formula is the range where the column C formula lives. I am worried about the increment supplied by the COLUMNS formula which is needed for the SMALL formula. Does this still occur within an array formula?