title: Dynamically referencing a cell
body: <p>I would like to set up Names for the drive, file path, Excel spreadsheet name and sheet name in cells in Excel, and then concatenate them together when referencing external cells.  This will allow me to create macros that reference sheets dynamically in the sense that it does not matter where the spreadsheet is. </p>
  
  <p>So far, I have the following:</p>
  
  <blockquote>
    <p>Drive = C<br />
    Location = Users\ashleys\Desktop\New Quote Production\Quote Location 3\Ash<br />
    Name = Book3<br />
    Sheet = Sheet1</p>
  </blockquote>
  
  <p>(Please note that the = sign above can be read as "assigned Name as")</p>
  
  <p>I concatenate these together to create another Name (called Final) in the following fashion:</p>
  
  <blockquote>
    <p>Final = "'"&amp;Drive&amp;":\"&amp;Location&amp;"["&amp;Name&amp;".xlsx]"&amp;Sheet&amp;"'!"</p>
  </blockquote>
  
  <p>Final resolves to be the following:</p>
  
  <pre><code>C:\Users\ashleys\Desktop\New Quote Production\Quote Location 3\Ash\[Book3.xlsx]Sheet1'!
  </code></pre>
  
  <p>So, as far as I am concerned, so far so good!</p>
  
  <p>However, I am embarrassingly stumbling at the last hurdle.  when I use the Final defined Name in cell references, it does not resolve in the way I expect.  So, if I am in Book1 and I use the code above to refer to cell A3 (which for example contains the word "Success!") in Book3, I would hope that typing the following into a cell in Book1 would resolve to "Success!":
  =Full&amp;A3</p>
  
  <p>Instead, the A3 resolves to A3 in Book1.</p>