title: Dynamically SUM across Excel sheets based on cell text value
body: <p>I am attempting to dynamically sum the same cell across multiple sheets based on the text content of another cell.
  In one cell, the file will always say "Data through [month name] [year]".
  I have a formula that extracts the month, but I can't figure out how to nest it into my other formula, which also works when it is static. </p>
  
  <p>Here is my formula to extract the month name:</p>
  
  <pre><code>=MID(A2,14,LEN(A2)-18)
  </code></pre>
  
  <p>Here is my formula to statically sum across sheets, which are named with month names:</p>
  
  <pre><code>=SUM(January:December!J5)
  </code></pre>
  
  <p>Here is the faulty formula that I am working with:</p>
  
  <pre><code>=SUM("January:" &amp; (MID(A2,14,LEN(A2)-18)) &amp; "!J5")
  </code></pre>
  
  <p>I have also tried: </p>
  
  <pre><code>=SUM(INDIRECT("January:" &amp; MID(A2,14,LEN(A2)-18) &amp; "!J5"))
  </code></pre>
  
  <p>Does anyone have any tips for me?</p>
  
  <p>Thanks in advance!</p>
  
  <p>EDIT:</p>
  
  <p>After editing, I now have a formula which will give me the sum of the first and last month, but not those in between. </p>
  
  <pre><code>=SUM(INDIRECT("January! J5"),INDIRECT(MID(A2,14,LEN(A2)-18)&amp;"!J5"))
  </code></pre>
  
  <p>In short, I need to replace that comma with the equivalent of a colon, though neither <code>:</code> nor <code>":"</code> works.</p>