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

	c	d
3	yes	18
4		19
5		19
6		19

<tbody>
</tbody>

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?