Excel 2010 formulas won't update till I edit -Auto Calc Set, - cell Format Correct

[jrozmaryn]

Hi All
I am using Excel 2010 on a 64 bit PC using Window 7 Enterprise
I input data via SQL Server to a blank Excel spreadsheet with some pre-populated formula cells.
The pre-populated formula cells point to the cells that SQL inputs, but are blank initially.
When I open the spreadsheet after the process completes, the input data is there, but the cells with equations are blank.
The equations are there, but will only produce results if I either edit the cell or press alt+ctl+F9.
It does not work if I just use F9.
The Calculate mode is set to automatic.
The cell is formatted to Number.
When I copy this file to another PC and open the spreadsheet, Same problem.
I tried to copy and paste to a new worksheet - same problem.
The original Excel spreadsheet was developed on another PC, (PC-1) using Excel 2010, and worked fine!
When I copied this file to PC-2 and rerun the process, the problem occurs.
I've searched the various forums, but all recommend the obvious, set auto calc.
I made the Advanced Options page on PC-1 exactly the same as PC-2, but I have the same problem.
I'm out of resolutions.
Help?

[dflak]

I'm grasping at a straw here, but highlight the reluctant columns and press F2 and then save the spreadsheet and try refreshing the data again.

Are these user defined functions? I have the same issue with some UDFs. If any code fails anywhere in the workbook all my UDFs seem to fail as well. Sometimes Application.Volatile helps.

[Kevin#]

How about forcing the re-calc when you open the file

Private Sub Workbook_Open()
Application.CalculateFull
End Sub

[jrozmaryn]

No, Just a simple formula. =IF(A3=0,"",1*A3), I've upload a screenshot of this problem. Note that the equation is in this cell but does not evaluate. Auto Calc is on, and I pressed f9 as well.
Jack

[Kevin#]

If suggestion in post#03 is not successful, here is something else to try:

Currently the formulas are in the workbook before the data is dumped. But the data dump does not trigger the calcluation.
So why not dump the data first and then add the formulas?

[jrozmaryn]

This run through an automated script. The script has worked fine for 2 years on a different PC, but when I switched to a new PC, the formulas stopped updating. Both are running Windows 7.
Jack

[Kevin#]

Did you try putting this in the ThisWorkbook module?

Private Sub Workbook_Open()
Application.CalculateFull
End Sub

(needs to be in the file before the data is added)

[jrozmaryn]

The problem with running a macro is that I would need to use .xlsm type file. Unfortunately, SQL Server 2008 does not support .xlsm files, only .xlsx files.

[Kevin#]

That's annoying.

[dflak]

Another desperate suggestion. Delete the columns containing the formulas and re-establish them. Don't just blank out the data, remove the columns entirely. You can probably copy and paste the formulas to notepad and then copy and paste them back into the formula bar.

[jrozmaryn]

I not only deleted the columns, I replaced each cell's formula with a simple "=a1" formula, but it still didn't work.
Now when I open this file in another PC, I do get the correct values. It seems that I must be missing a setting somewhere.

[dflak]

I guess you are right. Nothing to do but open the options side-by-side and compare what's checked for each of the versions of Excel.

[jrozmaryn]

I did that already. They match exactly. I was wondering if there may be a setting in Windows 7 that inhibits the auto calculations.

[Kevin#]

This does not solve the issue, but is a workaround.

Make the SQL update an empty file (= SQLdump.xlsx)

Have another file (= FileIWant.xlsm) which contains your formulas plus a macro to pull values from SQLdump.xls

If for any reasons formulas do not fire, then force them to update by including this as the last line in the macro

Please Login or Register  to view this content.