Prevent certain cells from recalculating

[Truck54]

Hello - I have a spreadsheet with columns for each of the 12 months of the year. The spreadsheet is utilized to access a maain frame system, and using 'DBRW' formulas, data is being pulled for the months completed thus far during the year. I then have a need to disconnect from that system and input data related to the months not yet completed, with formulas summing what I have inputted. The challenge is I am unable to calc the formulas associated with what I have inputted without recalcing the formulas associated with the months that were pulled using the 'DBRW' formulas. When I hit F9, it recalcs the whole sheet; thus, causing errors to populate the months where the 'DBRW' formulas were used to pull in data. This is due to the fact I am not connected to the system any longer so the 'DBRW' formulas run and gets an error. Is there a way to lock certain cells on a worksheet from recalcing when one hits F9? Ideally, I would like to be able to lock all cells from recalcing associated with the months completed thus far after I have done the pull from the system so when I hit F9 it would only be calcing/recalcing the formulas associated with the months I have inputted data. Hope this is not too confusing. Thanks in advance for any help!

 

[pgc01]

Hi
Welcome to the board

To do that requires some work at the design phase.

The idea is simple. You use 2 worksheets.

- One secodary sheet where you have all the formulas that will only be recalculated on demand.

- One primary sheet where you have all the formulas that are recalculated automatically plus you mirror the values from the cells in the secondary worksheet.

(Very) simple ex.:

You have

A2: =A1+1
A3: =A1*2

Let's suppose you want to recalculate A2 automatically but A3 manually.

You use a primary worksheet (Sheet1):

A2: =A1+1
A3: =Sheet2!A3

Now in the secondary worksheet (Sheet2), you have:

A3: =Sheet1!A1*2

Now you disable calculation in sheet2 and everytime you change Sheet1!A1, only Sheet1!A2 recalculates. Sheet1!A3 will not change value because it mirrors the value in Sheet2 and Sheet 2 has calculation disabled.

Whenever you want to update the value in A3, you just have to enable calculation in Sheet2 and then disable it again. This updates the value in A3, that will again ignore the automatic recalculation.


As I said, it's more a question of design.

HTH