Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Freezing a cell value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Freezing a cell value
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 On Jun 3, 3:46*pm, Jumbo Jock wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Freezing a cell value
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 On Jun 5, 9:53*am, Susan wrote: 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 On Jun 3, 3:46*pm, Jumbo Jock wrote: 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- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Freezing a cell value
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 "Susan" wrote: 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 On Jun 5, 9:53 am, Susan wrote: 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 On Jun 3, 3:46 pm, Jumbo Jock wrote: 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
freezing a cell or cells | Excel Worksheet Functions | |||
freezing cell values | Excel Worksheet Functions | |||
Freezing one cell | Excel Worksheet Functions | |||
Cell locking and program freezing | Excel Discussion (Misc queries) | |||
Freezing a cell range | Excel Discussion (Misc queries) |