Forcing an individual cell to manual calculation

[jnjcobb]

Hi,

I am working on a workbook that is a personnel managing tool. One of my sheets retrieves data from another sheet and displays it in cells d3-d6. All 4 cells use the same data source for their retrieval. Each cell represents a quarter of the year. When that quarter is completed I want to insert yes to c3 and prevent d3 from further updating itself (as shown in the table below).(The values shown below are arbitrary numbers that would normally have a formula in their place.)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]yes[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]19[/TD]
[/TR]
</tbody>[/TABLE]
I have also posted this on excel forum. The link to that post is below:
How can I freeze a value in a cell with a formula without removing the formula.
The worksheet I attached to that forum has different values to be in the c3 block, but I am not worried about that, because I can change that very easily.

The code I have written for this is below, but I have not gotten any result out of this. I am typing it into VBE under my sheet 5 module.

Sub Personnel_Tracker()    
    If Range(Sheet5!c3).Value = "Yes" Then
        Range(Sheet5!d3).Calculation = xlCalculationManual    
    Else: Range(Sheet5!d3).Calculation = xlCalculationAutomatic    
    End If
End Sub

This tracker is for my military unit, and I am trying to improve on its capabilities. I am out of my league with this, but am hoping I can at least get some solid direction for where to look/go for help.

Thanks in advance for any help with this!

 

[jnjcobb]

Can anyone tell me if this is even possible?