sbLockedFormula

Normally Excel © adjusts all cell references of a formula if you add or if you delete rows or columns before these cells your formula is referring to.

But what if you do not want this? A single cell reference you can lock with the INDIRECT command.

Here are two approaches for a whole formula:

1. Enter your formula normally into a cell and test it to ensure that it is working correctly.

2. Now let us implement a VBA solution. Press ALT + F11 and enter a new module. Copy this macro into that new module:

Please read my Disclaimer.

Function sbLockedFormula(s As String) As Variant
'Evaluates s as a formula.
'Reverse("moc.LiborPlus.www") PB 28-Oct-2010 V0.10
Application.Volatile 'Necessary since we do not
                     'know when to recalculate
sbLockedFormula = Evaluate(s)
End Function

3. Go back to your worksheet Sheet1 and enter:

4. You will notice that you need to enclose your formula by double quotes and that you need to replace each " (double quote) by two "" (two double quotes).

5. This VBA approach is my preferred one.

6. But if you are not allowed to use VBA then let us go with a "worksheet formula"-only approach which makes use of the Excel4 macro command Evaluate.

7. Define a name EvaluateLockedFormula which refers to =EVALUATE("IF(AND($A$2>=Master!$X$2,$A$2<=Master!$CK$2),Master!$DE$2,"""")")

8. Now enter into your desired result cell =EvaluateLockedFormula

9. Please notice that you need to recalculate this cell after row or column additions or deletions with CTRL + SHIFT + F9, not only with F9 (of course you can also select your result cell and press first F2, then ENTER, to recalculate only this cell).