title: Reference another sheet by cell variable?
body: <p>What I am trying to do is to create a formula which references another sheet.  Rather than adding the name of the sheet directly, I want to be able to have the sheet name as a variable.</p>
  
  <p>My formula is as follows:</p>
  
  <pre><code>=IF(B2="",(HLOOKUP($A$2,'Sheet 1'!$C$5:$AB$200,2)),(HLOOKUP($B$2,'Sheet 1'!$C$5:$AB$200,2)))
  </code></pre>
  
  <p>Sheet 1 is the sheet that is being referenced statically here, however cell A5 will be the cell which determines which sheet to look at. Rather than say 'Sheet 1' it needs to be something similar to =A5</p>
  
  <p>I have read about using INDIRECT, however am unable to get this to work</p>
  
  <pre><code>=IF(B2="",(HLOOKUP($A$2,(INDIRECT(CONCATENATE("'",A5,"'"))!$C$5:$AB$200,2)),(HLOOKUP($B$2,(INDIRECT(CONCATENATE("'",A5,"'"))!$C$5:$AB$200,2)))
  </code></pre>
  
  <p>If anyone can assist, that would be much appreciated</p>