title: Excel: How to create (static) array constant {1,2,3,...,100}
body: <p>I'd like to create a named array constant containing <code>{1,2,...,100}</code> in excel that is static and unchanging. I can do this manually for a short array like <code>{1,2,3,4,5}</code>, but I can't figure out a formulaic method.</p>
  
  <p>I've tried to do this with the <code>ROW()</code> command by entering <code>=ROW(1:100)</code>, but this "constant" gets altered if I add or remove spreadsheet rows. For example, if I add a row between rows 2 and 3 the formula will become <code>=ROW(1:101)</code>.</p>
  
  <p>The only method I've discovered is to use the <code>INDIRECT()</code> function, </p>
  
  <pre><code>=ROW(INDIRECT("A"&amp;1):INDIRECT("A"&amp;100))
  </code></pre>
  
  <p>but I can't imagine this is the recommended way to achieve this.</p>
  
  <p><strong>[Edit]</strong> A few more details to help clarify. I don't want to create this array anywhere on my spreadsheet. I just want to reference that range of numbers it in a formula.</p>
  
  <p>My current desire to create such a named array constant is the following use case. Suppose I have a bunch of integers in column A and I want to determine the smallest in the range {200, 201, ... 300} that is <em>not</em> present in column A and put that value in cell B1. My method is</p>
  
  <ol>
  <li>Create a named array constant say <code>'targetRange' =ROW(INDIRECT("A"&amp;200):INDIRECT("A"&amp;300))</code></li>
  <li>Place the following formula referencing the named array in B1: <code>=SMALL(IF(COUNTIF(A:A,targetRange)=0,targetRange,""),1)</code>.Create a named array constant say <code>'targetRange' =ROW(INDIRECT("A"&amp;200):INDIRECT("A"&amp;300))</code></li>
  </ol>