title: Every time a Specific Worksheet is Calculated in a loop Excel Crashes After 19 Iterations of loop
body: <p>My workbook I designed has a data consolidation sheet where a good amount of data is processed and organized.  This specific sheet has about 42 spill array formulas. Whenever I re-calculate the page after I open the file, the RAM usage of excel gots from about 650MB to 12GB. Then after one more calculation it maxes out at 20GB. The RAM is only 16GB and the extra 4GB is virtual memory. The reason why multiple calculations are done is because I have loop macro I made to automate a calculation necessary for my company.</p>
  <p>Here are the spill formulas:</p>
  <p><code>=SORTBY(FILTER('Loss Template'!$E:E, ('Loss Template'!$A:$A=TEXT($B$2,&quot;0&quot;))*('Loss Template'!$F:$F&lt;=$J$7+1)*('Loss Template'!$F:$F&gt;=$H$7)*('Loss Template'!$H:$H&gt;2000)*('Loss Template'!$M:$M=5),&quot;&quot;),FILTER('Loss Template'!$M:$M, ('Loss Template'!$A:$A=TEXT($B$2,&quot;0&quot;))*('Loss Template'!$F:$F&lt;=$J$7+1)*('Loss Template'!$F:$F&gt;=$H$7)*('Loss Template'!$H:$H&gt;2000)*('Loss Template'!$M:$M=5),&quot;&quot;),1,FILTER('Loss Template'!$H:$H, ('Loss Template'!$A:$A=TEXT($B$2,&quot;0&quot;))*('Loss Template'!$F:$F&lt;=$J$7+1)*('Loss Template'!$F:$F&gt;=$H$7)*('Loss Template'!$H:$H&gt;2000)*('Loss Template'!$M:$M=5),&quot;&quot;),1)</code></p>
  <p><code>=IF(G10#&lt;&gt;&quot;&quot;,5,&quot;&quot;)</code></p>
  <p><code>=IF($G10#&lt;&gt;&quot;&quot;,IF(XLOOKUP($G10,'Loss Template'!E:E,'Loss Template'!G:G,,0)=&quot;Closed&quot;,&quot;F&quot;,&quot;O&quot;),&quot;&quot;)</code></p>
  <p><code>=IFERROR(INDEX('Loss Template'!E:H,MATCH(G10#,'Loss Template'!E:E,0),4),&quot;&quot;)</code></p>
  <p><code>=IF(J10#&lt;&gt;&quot;&quot;,IF(J10#&gt;'Loss Template'!$P$2,'Loss Template'!$P$2,J10#),&quot;&quot;)</code></p>
  <p><code>=IF(K10#&lt;&gt;&quot;&quot;,ROUND(K10#,0),&quot;&quot;)</code></p>
  <p><code>=IF(J10#=&quot;&quot;,&quot;&quot;,IF(AND(J10#&gt;=143000,J10#&lt;&gt;&quot;&quot;),143000,J10#))</code></p>
  <p>These formulas are just copied, with slight variations in references, 4 other times on the same sheet.  In this case, is having full column references the cause of my woes? As you see here each long <code>sortby</code> has a lot of these references so I would think that could be the cause. I do know with some formulas like index you can use full column references without any calculation time loss.</p>
  <p>Here is a screenshot of one third of the table. The magnitude of the calculations is 3 times what you see here.</p>
  <p><a href="https://i.stack.imgur.com/9tpm5.jpg" rel="nofollow noreferrer">Worksheet Image</a></p>
  <p>Given this information, how can I reduce RAM usage to normal or manageable levels?</p>