Excel Calculation problem 1

[MJV8198]

I have an ongoing problem with calculations not calculating. I have a very large workbook, 20+ sheets, I am using named formulas. I have "enable iterative calculation" checked.

The problem I have is some of the formulas do not calculate. If I use a named formula 100 times and it does calculate none of the cells using the formula calculate. If I copy the named formula from one cell to another the receiving cell will calculate but the other cell will not calculate. If I go into the named formula and make a minor change for example

formula name "Total"
actual formula =A1+B1+C1

If I go to the named formula and change the formula to =A1+B1 all the cells using that name will update with the new formula. If I add back what I removed it will recalculate with the old formula.

I am stumped, any ideas?

 

[combo]

The "enable iterative calculation" feature allows calculations for circular references. Is it your case?
By "named formula" you mean named range, name with formula in "refers to" (in names manager) or custom function?
Can you upload sample workbook with some dummy data that illustrates your problem?

combo

 

[SkipVought]

What happens when you do...

Formulas > Defined Names > Name Manager...

...and look for the FORMULA for Total.

What is the FORMULA in the Name Manager?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

Repair?

https://support.office.com/en-us/ar...&rs=en-US&ad=US#ID0EAADAAA=Windows_7_or_Vista

 

[MJV8198]

Answers to "Combo" questions:

1)Yes I have circular references
2)I have both named ranges and named formulas. Some of the formulas use the named ranges however some formulas calculate with named ranges some don't, same with formulas without named ranges
3)Name formula is a formulas we wrote using standard excel formulas, we created and named the formula name manager and can be found in formula-Name manager
4) I am working on a slimmed down version with sample data to upload

Answer to Skip
If I look at name "test" I see =A1+B1+C1

A couple other points.
I use office 365 so it is the current version of excel
This occurs on multiple computers so I don't believe it is a corrupt install
It is a xlsm workbook
I download it from SharePoint.

Additional testing I have performed.

If I make the changes as I noted about the entire workbook will calculate
If I drag the formula it does not calculate If I copy a cell and re-paste in the same cell it doe not calculate
If I copy the entire sheet and repast it in place all formulas recalculate on that sheet but other sheets do not calculate.

 

[combo]

It will be helpful to see the workbook.
Your formula is without dollar signs, it means it is relative. Referenced cells change relatively to active cell / caller cell.

combo

 

[MJV8198]

Hello Everyone,

Thank you for your help and I apologize for the delay in providing an update. However I believe I have discovered the problem and devised a solution.

To provide some insight, the sheet with the formulas read data from data sheets within the workbook. These data sheets are updated from time to time. These data sheets were being updated using a paste macro.
While we were sorting out this calculation problem we continued to develop the data sheets would import the data from an access database. We noticed when we ran the update for the data sheet from the access program (via a Macro), the formulas initially calculated but when complete the the data would disappear. Sorry I will be a little vague here, I believe if we did the update via a manual process either we had the same problem or an intermittent calculation problem. We were trying so many different things I did not properly keep tract of the problems.

What we found is we eliminated the problem by changing the formula Calculation from Automatic to Manual updating the data sheets either via the refresh data or by copy and pasting then turning the calculation back to automatic, all formulas calculated properly.

So wrote we wrote macro that turned Calculation to Manual did the refresh by paste or access and turned switched the Calculation to auto. Same problem.
We broke it into 2 macros segregating out the return final step, turning Calculation back to automatic in a separate macro. Ran macro 1 ran macro 2 ....same problem.

So the solution was to run macro 1 which turn Calculation to manual and refreshes the data. The final code instructs the user to manually set the Calculation to automatic. When you turn the calculation back to Automatic by a manual process by the user the formulas calculate properly.

We can live with this solution but if anyone has an idea why this happens I be interested in hearing your thoughts.

Thank you very much for your help.


Edit- In this process I found and corrected my Circular references- this was prior to finding the solution and did not resolve the problem.

 

[SkipVought]

Thank you for painstakingly explaining the process that you traversed and the results that you observed in order to arrive at a solution, which you graciously shared with your fellow members.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]