title: Excel Reference External Sheet
body: <p>An excel question for you gurus. I've tried searching high and low and haven't come up with an effective solution. </p>
  
  <p>I'm trying to create a formula that will lookup a value in an external sheet. I'm using the SUMPRODUCT formula and it works perfectly. Formula is below:</p>
  
  <pre><code>=SUMPRODUCT(--('File\Path\[file.xlsx]SheetName!$D$1:$D$1000=$B3), --('File\Path\[file.xlsx]SheetName'!$O$1:$O$1000=$A3), 'File\Path\[file.xlsx]SheetName'!$Q$1:$Q$1000)
  </code></pre>
  
  <p>The issue I'm running into, however, is that the source file is updated every day. Although the workbook name stays the same, the sheet name changes. A random string gets assigned to the source sheet name each time it is updated. As such <code>SheetName</code> becomes <code>SheetName ase341</code>.</p>
  
  <p>Is there a way to have the formula read the external sheet number instead of the name? I want the formula to update regardless of the sheet name. If there's no way to read the sheet position is there a way to change the sheet name via a formula in an external workbook? </p>
  
  <p><br /></p>
  
  <hr>
  
  <h3>Usage Example</h3>
  
  <p>I have a workbook (analysis) and it pulls data from another workbook (source). Source is updated every day with new data. The data in Source is updated by downloading a report from the internet and saving over the old source file. As such, the file name stays the same but whatever is inside the file is always different (including the sheet name). There is always only ever one sheet in the Source with the same number of columns, always in the same position.</p>