title: Excel VBA inserting formula with variable that references a different sheet links to saved file rather than open file
body: <p>I have this line in a long sub and as title mentions instead of using values from the open workbook it uses the saved file and every time it runs it requires manual linking to file.
  MC is a workbook APs is a workbook name as a string cl is a range.</p>
  
  <pre><code>MC.Sheets("MasterC").Range("$Z$1").Value = "='[" &amp; APs &amp; " ]Stock'!" &amp; cl.Offset(0, 11).Address 
  </code></pre>
  
  <p>this is a line in a for loop where cl is a single cell in a range.So can't use absolute values. I've used <code>.formulaR1C1</code>instead of <code>.value</code> to no avail. </p>