title: Why do Dynamic Named Ranges not work with INDIRECT()?
body: <p>I am unable to use the <code>INDIRECT()</code> function in Excel with some kinds of named ranges. Simple named ranges seem to work okay, but when I have something more complex, I get hit with a <code>#REF</code> error.</p>
  
  <p>Here's an example:</p>
  
  <p><a href="https://i.stack.imgur.com/jsCcz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jsCcz.png" alt="Example of INDIRECT not working"></a></p>
  
  <p>Here, the basic named range <code>SomeData</code> works just fine with both <code>SUM</code> and <code>INDIRECT</code>. It is just a basic collection of the three values there</p>
  
  <p>The more complex named range <code>MoreData</code> works with a regular <code>SUM</code>, but when trying to use it in an <code>INDIRECT</code> function, it stops working, with no clear reason why.
  You can see what the MoreData function is there, but in essence, it's meant to take the last 6 entries from the table labeled <code>tbl_MoreData</code>.
  Here is the formula used for <code>MoreData</code>:</p>
  
  <pre><code>=OFFSET(tbl_MoreData[[#Headers], [MoreData]],COUNT(tbl_MoreData[MoreData]),0,(IF(Main!$N$13&lt;COUNT(tbl_MoreData[MoreData]), Main!$N$13, COUNT(tbl_MoreData[MoreData]))*(-1)),1)
  </code></pre>
  
  <p>I hope my explanation was not too cluttered. Please comment if something is still unclear and I hope to be able to explain myself better.</p>