title: How to preserve a formula's reference to a worksheet when the worksheet is deleted and replaced?
body: <p>I am the author of an application that before opening an Excel workbook, deletes and recreates some worksheets, like, for example "Sheet1" in the example below.</p>
  
  <p>Other worksheets (say Sheet2) in the same workbook may have formulas that refer to the replaced worksheet, like this:</p>
  
  <pre><code>=IF('Sheet1'!A9="","",'Sheet1'!A9)
  </code></pre>
  
  <p>Unfortunately, the reference in the above formula to Sheet1 is broken when the sheet is replaced, becoming </p>
  
  <pre><code>=IF(#Ref!A9="","",#REF!A9)
  </code></pre>
  
  <p>Can anyone think of a way to code this formula so it doesn't fail?  It could, for example, get the name of the worksheet from a hidden cell in Sheet2 or something?</p>
  
  <p>I just don't know enough about Excel formulas to know what the possibilities are.</p>
  
  <p>TIA</p>