title: How do I write a an index(concatenate) function in Excel
body: <p>First, I know this topic is widely posted, and I've viewed 12+ hours of examples and I can't quite get this to work.  I'm trying to concatenate a path\filename\sheet\cell to reference when the workbook is closed.  Indirect works but only when the workbook is open.  Relative to many of the examples found, this is a seemingly simple one.</p>
  
  <p>This works independently of CONCATENATE</p>
  
  <pre><code>=INDEX('C:\temp\[ABC.xlsx]Sheet1'!A:C,2,2)
  </code></pre>
  
  <p>This works independently of INDEX.  The filename "ABC" is found in cell "A2"</p>
  
  <pre><code>=CONCATENATE("'C:\temp\[",A2,".xlsx]Sheet1'!A:C")
  </code></pre>
  
  <p>The combination of INDEX and CONCATENATE yields "#VALUE!</p>
  
  <pre><code>=INDEX(CONCATENATE("'C:\temp\[",A2,".xlsx]Sheet1'!A:C"),2,2)
  </code></pre>
  
  <p>Basically, I'm trying to reference cell B2 in a file named ABC.xlsx in worksheet "Sheet1".  This above combination is what I need to work correctly.</p>