title: Referencing File Path variable in Index/Match formula from different workbook with Excel VBA
body: <p>Good day,</p>
  
  <p>I'm trying to reference a file path variable in Index/Match formula with Excel VBA.
  Everything works up to the Index/Match formula and I think the file path and worksheet reference is the problem.
  Currently, it's giving the error "Run-time Error '1004': Application-defined or object-defined error"</p>
  
  <p>This is just a section of the code since a lot happens with this macro and I have to do the same thing for 8 different files, with different names and different sheet names. Different people will use the macro, and the main files will be in different folders, therefore I have to add the variables for the file paths and worksheets.</p>
  
  <p>I've read many posts trying to find a solution. A lot suggests using the <strong>indirect</strong> or <strong>indirect.ext</strong> function/formula but apparently with <strong>indirect.ext</strong> you have to enable an add-on, which I can't do since many different people will use the macro. And the <strong>indirect</strong> is apparently volatile. Since this formula will be used in at least 8 different columns and over 50 000 lines, this doesn't seem like a very good idea for processing time.</p>
  
  <p>The external workbooks will be opened with code before this formula runs.</p>
  
  <p>This is the section of code for the Index/Match formula.</p>
  
  <pre><code>Dim path As String
  Dim StoreFile As String
  Dim StoreFileF As String
  
  path = GetFolder()
  
  StoreFile = Dir(path &amp; "\*Store and Format*.xls*")
  StoreFileF = path &amp; "\" &amp; StoreFile
  
  With ws.ListObjects("Table_SDCdata")
      With .ListColumns.Add
          .Name = "Region"
      End With
      .ListColumns("Region").DataBodyRange.Formula = "=INDEX('[" &amp; StoreFileF &amp; "]Worksheet[1]'!F:F,MATCH([@Site],'[" &amp; StoreFileF &amp; "]Worksheet[1]'!A:A)0,1)"
  End With
  </code></pre>
  
  <p>Any suggested or help will be appreciated.</p>