title: Excel - Create dynamic sum formula range
body: <p>I am currently using the following formula</p>
  
  <pre><code>=SUM(INDIRECT("'Net "&amp;($A$2)&amp;" "&amp;RIGHT($A48,2)&amp;"'!C4:C21"))
  </code></pre>
  
  <p>That works out perfectly, but I'm trying to make the range portion of it (<code>C4:C21</code>) dynamic.</p>
  
  <p>The <code>$A$2</code> that the formula is referencing is something that someone types in and currently says "May". The $A48 that the formula is referencing is simply pulling the last 2 digits of the year.</p>
  
  <p>These are the pieces of the file</p>
  
  <p>There are a total of 6 tabs that are significant. There are 4 tabs that total up prior years sales for a particular month. So I have Net May 11, Net May 10, Net May 09, Net May 08. These are the tabs that the Indirect formula is finding.</p>
  
  <p>The last 2 tabs includes the one where this formula exist in (Net May 12) and the tab that the range will need to be based off of (Cust May 12). In the Cust May 12 tab Column A is the day, column B the day of the Week, and column C is where we have our sales data. Someone goes in every day and types in the sales for the day. So as an example I currently have sales data from 5/1/2012 until 5/18/2012. Everything from 5/19/2012 - 5/31/2012 in column C is blank. The sales data that currently exist are in <code>C4:C21</code>, with <code>C22:C34</code> being blank. Because I am comparing prior years sales to this years sales they must be comparable, which is why the formula only uses <code>C4:C21</code> for all of the prior years sales.</p>
  
  <p>I have tried a few things including a CountA and some offsets to try and help me out, but I have been unable to find a solution to making the range dynamic for all of my prior years sales formulas. I need the formula to simply increment to something like <code>C4:C22</code> for all of the prior years when someone types in the sales data for the next day in the Cust May 12 tab.</p>