title: Using IMPORTRANGE, INDIRECT, and CONCATENATE together
body: <p>I have a sheet that pulls numbers from several different sheets to amalgamate numbers. Each week, a new sheet is added to the source files, all with the same name. I'd like to update the amalgamated sheet by changing one cell instead of many.
  When the tab is located in the same Google sheet, this is easily done with INDIRECT.</p>
  
  <p>Right now, the formula in the amalgamated sheet is:</p>
  
  <pre><code>=IMPORTRANGE(M4, "Aug29!$F$2")
  </code></pre>
  
  <p>That formula is on each line to pull from several different sheets:</p>
  
  <pre><code>=IMPORTRANGE(M5, "Aug29!$F$2")
  =IMPORTRANGE(M6, "Aug29!$F$2")
  </code></pre>
  
  <p>and so on. </p>
  
  <p>Each week, the "Aug29" changes to the new date - "Sep5", "Sep12", etc.</p>
  
  <p>What I'd like to do is use INDIRECT to pull that part of the equation in (from cell Z1) so that I don't have to update each formula. </p>
  
  <p>I was thinking I could use CONCATENATE to create the "Aug29!$F$2" portion of the formula with INDIRECT:</p>
  
  <pre><code>=IMPORTRANGE(M4, CONCATENATE(INDIRECT(Z1), "!$F$2"))
  </code></pre>
  
  <p>The problem is, the IMPORTRANGE formula requires quotations around the range string, and I can't figure out how to add them. </p>
  
  <p>Weird problem, I'm sure, but wondering if there's anyone with a solution?</p>