title: The range in the Excel formula changes after insert-shift, how can I stop it changing?
body: <p>I have an Excel 2010 formula in an Excel cell like this (the formula lies in column T):</p>
  
  <pre><code>=SUMIF($B$14:$B$44335;$T8;$E$14:$E$44335)
  </code></pre>
  
  <p>This formula does this: It finds the cells in the <code>Range("B14:B44335")</code> which have the value as <code>Range("T8")</code> (For example, between <code>Range("B192:B370")</code>), then it sums up the values in the column E (For example, sums up between <code>Range("E192:E370")</code>).</p>
  
  <p>The formula works fine, but a background VBA-code couses problem. This VBA code sometimes extends or shorten <code>Range("B14:E44335")</code>. The VBA-code uses <code>insert shift:=xlDown</code> or <code>delete shift:=xlUp</code> for extending or shortening.</p>
  
  <p>Insert shifting (inserting 178 rows) in VBA causes that the formula changes like this:</p>
  
  <pre><code>=SUMIF($B$192:$B$43623;$T8;$E$192:$E$43623)
  </code></pre>
  
  <p>So the formula also shifts from <code>B14</code> to <code>B192</code>.</p>
  
  <p>This is strange, because the formula is in the column T, and the VBA-code has nothing to do with the column T. </p>
  
  <p>I tried to give a name to the range like, when I write <code>Countries</code>, then Excel selects the <code>Range("B14:E44335")</code>, so</p>
  
  <pre><code>=SUMIF(Countries;$T8;$E$14:$E$44335)
  </code></pre>
  
  <p>But i got the same problem again. After <code>Insert Shift</code>, <code>Countries</code> shows <code>Range(B192:B43623")</code></p>
  
  <p>In anyway, how can I keep the range in my formula permanent, no matter what happens in other cells? </p>