I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

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.

20101028_PB_01_sbLockedFormula_Screen
20101028_PB_02_sbLockedFormula_Screen

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

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:

20101028_PB_03_sbLockedFormula_Screen

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,"""")")

20101028_PB_04_sbLockedFormula_Screen

8. Now enter into your desired result cell
=EvaluateLockedFormula

20101028_PB_05_sbLockedFormula_Screen

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).

Sulprobil   Get it done   Contact   Disclaimer   Download