title: Linking cells to different worksheet by using vba to insert dynamic formulas
body: <p>I am creating a new worksheet called varRef and am trying to link cells of an existing worksheet to this new worksheet by using VBA. I have been trying to solve it for a couple of hours but cannot get it right and cannot find a similar case on the web either.</p>
  
  <p>The code is:</p>
  
  <pre><code>Dim varRef as Variant 'name of the new sheet
  varRef = Inputbox("xyz") 'this is how I define the name
  Dim intRow As Integer 'row that corresponds to the appropriate postion in the existing sheet
  intRow = ActiveCell.Row 'see above
  Cells(intRow, 22).Formula = "=IF(varRef &amp; ""!I148""="""","""",varRef &amp; _
  ""!I148"")" 'trying to link the contents within the same workbook
  </code></pre>
  
  <p>The result in the cell I get is</p>
  
  <pre><code>=IF(varRef &amp; "!I148"="","",varRef &amp; "!I148"
  </code></pre>
  
  <p>which is obviously not working.</p>
  
  <p><strong>Issue 1)</strong> is that VBA does not recognize my variable in the formula. It is working for naming the sheet however.</p>
  
  <p><strong>Issue 2)</strong> is the quotation marks, that are not working as intended. One is supposed to use double quotation marks to not end the string. However the second marks will not disappear in the final code of the cell.</p>
  
  <p>Any help is very much appreciated and hopefully valuable for other users as well!</p>