title: INDIRECT inside INDEX function giving #REF error
body: <p>I'm have an excel workbook <strong>Spreadsheet.xlsx</strong> in which <strong>B2</strong><code>=MATCH(B1,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$1:$A$500,0)</code> takes the string (a name) in in <strong>B1</strong> and matches it to a column of row headers in <strong>Workbook.xlsx</strong>, to give the row number.</p>
  
  <p>Currently <strong>B2</strong> returns a value of <strong>8</strong>, which is used in <strong>B3</strong><code>=INDEX('\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$8:$SD$8), MATCH($A4,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$2:$SD$2,0))</code>.</p>
  
  <p><strong>B3</strong> indexes a range of values <code>$A$8:$SD$8</code> corresponding to the row number from <strong>B2</strong> in <strong>Sheet1</strong> in <strong>Workbook.xlsx</strong>, and returns the values matched to a date <code>$A4</code> in the range <code>$A$2:$SD$2</code>.</p>
  
  <p>Currently I am manually typing in the row number to <strong>B3</strong> range `$A$8:$SD$8. </p>
  
  <p>If I try to automate this using <strong>B3</strong><code>=INDEX(INDIRECT("'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet'!$A$"&amp;B2&amp;":$SD$"&amp;B2), MATCH($A4,'\\SERVER\Path\To\File\[Workbook.xlsx]Sheet1'!$A$2:$SD$2,0))</code>, I get a <code>#REF</code> error. </p>
  
  <p>I have used <code>INDIRECT</code> elsewhere e.g. <code>=MAX(INDIRECT(K&amp;"K1"))</code> and it has worked just fine.
  Also, <strong>Workbook.xlsx</strong> and <strong>Sheet1</strong> in it definitely exist, and I am able to get data from them without using <code>INDIRECT</code>.</p>
  
  <p>Why doesn't it work?</p>