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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]X
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]SYMBOL
[/TD]
[TD]SUM
[/TD]
[TD]QUANTITIES
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]=IFERROR(INDEX(ATTOUT!$D:$D,MATCH(0,COUNTIF(ATTOUT!$D:$D,"<"&ATTOUT!$D:$D)-SUM(COUNTIF(ATTOUT!$D:$D,$A$1:A1)),0)),"")
[/TD]
[TD]=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))
[/TD]
[TD]=IFERROR(INDEX(ATTOUT!$C:$C,SMALL(IF(ATTOUT!$D:$D=$A2,
ROW(ATTOUT!$C:$C)-ROW(ATTOUT!$C$1)+1),COLUMNS($C:C))),"")
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Ctrl+'
[/TD]
[TD]Ctrl+'
[/TD]
[TD]Ctrl+'
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]X
[/TD]
[TD]C[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]QTY
[/TD]
[TD]SYM
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]779 SF
[/TD]
[TD]CONC
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1230 SF
[/TD]
[TD]REF
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]345 SF
[/TD]
[TD]DG
[/TD]
[/TR]
</tbody>[/TABLE]

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?