title: Excel SUM (across mutiple worksheets) Function Returns #REF When Using Indirect
body: <p>I am currently working on an Excel 2010 workbook with a worksheet for each region I do business in containing the estimated man hours for the next X weeks, with a final worksheet adding the sum of each week for each region.</p>
  
  <p>I have completed the majority of the workbook with just one formula holding me back:</p>
  
  <pre><code>=SUM(San_Diego!F32+Inland_Empire!F19+Los_Angeles!F26+Over_The_Horizon!F18)
  </code></pre>
  
  <p>The above formula returns the correct SUM perfectly but automatically corrects itself when someone adds a new column into one of the worksheets. I have attempted a few other options that have been recommended over the past week I have been working on this workbook:</p>
  
  <pre><code>=SUM(San_Diego!$F$32+Inland_Empire!$F$19+Los_Angeles!$F$26+Over_The_Horizon!$F$18)
  </code></pre>
  
  <p>The <code>$</code> does not work at all and still auto corrects.</p>
  
  <p>I've also tried adding <code>INDIRECT</code> but that doesn't work either and gives a #REF error</p>
  
  <pre><code>=SUM(INDIRECT("San_Diego!E32"&amp;"Inland_Empire!E19"&amp;"Los_Angeles!E26"&amp;"Over_The_Horizon!E18"))
  </code></pre>
  
  <p>The cells being referenced do have the following simple SUM formula or an equivalent in them:</p>
  
  <pre><code>=SUM(E3:E17)
  </code></pre>
  
  <p>I'm hoping (and quite sure) that I have just totally missed a concept and it is a simple error I am making over and over.</p>