Freezing a cell value

[Jumbo Jock]

Help desperately required.

I have a spreadsheet that takes a collection of user inputs to generate a
value that managers in my org will use as a guide to providing promotional
increases to employees. However, managers have the flexibility to choose a
different value if they want and so directly below the calculated cell value
is a free cell that managers can make their actul recommendation in.

Only trouble is.....this free cell is linked to some of the original
variable inputs that created the original input and so, depending on what
value the managers choose to enter, the original recommendation can change
and I don't want that.

So, problem of the day is as follows:- is there any way in excel to freeze a
cell value once it has been calculated for the first time so that any further
changes to input variables will not change the answer?

Many thanks

 

[Susan]

the only thing i can think of is a worksheet_change or
worksheet_calculate macro that will copy that cell (say it's A1) and
paste the VALUE, eliminating the formula, so that additional changes
in other cells won't have any effect on A1.
but that would be tricky to know exactly when to do this copying &
pasting. you'd have to link it to another cell that generally gets
filled in in order to calculate A1.
hope that helps
susan

 

[Susan]

what i came up with is this small scenario:

cell b3 contains a formula which says =sum(e2+e4+e6).
they get filled in in order. when cell e6 gets filled in, the formula
in b3 becomes the value of the calculation & no more calculations can
take place.
=====================
Private Sub worksheet_calculate()

Dim ws As Worksheet

Set ws = ActiveSheet

If ws.Range("e6").Value = "" Then
Exit Sub
Else
ws.Range("b3").Value = ws.Range("b3").Value
End If

End Sub
====================
hope it gives you an idea of a way to try.
susan

 

[Jumbo Jock]

Hi Susan

That's brilliant - not exactly what i need but a fantastic start and I
should be able to get this problem fixed now!

Many, many thanks