Locking/Freezing Formula

[vadayil]

My question is pretty simple, but one to which I can't really find a solution myself. I use Excel quite frequently, but rarely the fancy stuff. Anyway, to the point:


How do I create a dropmenu that will lock certain cells, rows or columns? (by lock I mean unable to change via hand and stops updating itself).


Example: A1 = 5, B1 = A1 * 100, C1 = A1 * 100


Can I "lock" the B1-cell, so that changing A1 will have no effect on B1 (Will remain at value 500), but will normally affect C1-cell (will update and calculate new value)?


I would preferably want to be able to "unlock" the cell as well, instead of simply making function into value.


Is this possible?


Thanks in advance!

 

[SirJB7]

Hi, vadayil!

Better later than never, isn't it?

Despite I don't see the utility of this, and don't considering that you don't explain what does the drop menu should behave (as described it's irrelevant if there's a drop control or not, only A1:C1 cell values count), you can play around with something like this:

a) set iterative calculation on (File tab, Options entry, Formulas page, Calculation Options section, and click on Enable Iterative Calculation)

b) change formula in B1 to:

=IF(B1=0,A1*100,B1)

That should keep the original value first calculated in B1 with the first value that took A1 and maintain it thru new changes in A1 cell, until you edit (F2) and press enter again in cell B1.

Regards!

 

[Luke M]

Echoing the same sentiments as SirJB7 as to why you'd want to do this, the other option I see is making the whole sheet set to manual calculation, and then using macros to control which cells get recalculated based on the drop-down value. Tricky, but doable.

 

[NARAYANK991]

Hi Vadayil ,


You can probably try out a macro which is on the following lines :

[pre]

Public Sub Freeze_Formula()
ActiveCell = "=" & ActiveCell.Value & "+N(""" & ActiveCell.Formula & """)"
End Sub

Public Sub Unfreeze_Formula()
ActiveCell = Mid(ActiveCell.Formula, InStr(1, ActiveCell.Formula, """") + 1, InStr(Len(ActiveCell.Formula) - 3, ActiveCell.Formula, """") - InStr(1, ActiveCell.Formula, """") - 1)
End Sub

[/pre]
I am not sure that it will work for all formulae , but for the simple one that you have posted , it does.


Narayan