In Automatic Calculation Mode, spreadsheet still does Manual Calculation??

[crinas]

I have a spreadsheet that is in manual calc mode and has a data list. Once I upload the data, I want to remove the formulas and data list and change to automatic calculation so I do a copy/paste values on the formula tabs and then delete the data list tab and change the spreadsheet to automatic and save it.

Problem - even though it says it's in auto mode, I have to hit enter twice for it to do the calculation when I enter a number and it shows that it's doing manual calculation in the bottom right corner and when I save the spreadsheet it does a manual calculation first BUT it's set to auto calculation. Help?

Excel 2010

Thanks

[SamT]

This Kludge might help
Private Sub Workbook_Open()
Application.Volatile
End Sub

[offthelip]

it sounds like you might need to force excel to rebuild the calculation tree, you can force it to do that by the following ( From Microsoft) Try doing this before you save the file.

Workbook Tree Rebuild and Forced Recalculation

Keystroke: CTRL+ALT+SHIFT+F9 (introduced in Excel 2002)
VBA: Workbooks(reference).ForceFullCalculation (introduced in Excel 2007)
C API: Not supported


All modes
Causes Excel to rebuild the dependency tree and the calculation chain for a given workbook and forces a recalculation of all cells that contain formulas.

[crinas]

Thanks SamT. I guess I should have said I'm not a coder so I don't know what Kludge is or how to do what you've suggested. ;(

[crinas]

Hi offthelip. Maybe I'm not understanding your reply but I don't want it to do manual calculations after I remove the data list and formulas. I've switched it to Automatic Calculations but it still does Manual Calculations. I tried the Keystroke thing before I saved the file without the data list and then when I opened it again, it did a manual calculation. I'm not a coder so not sure I'm understanding.

[offthelip]

if you tried the "key stroke thing" and it didn't work it probably means my guess was wrong, another possible "kludge" which doesn't require programming is to put the current time/date into a cell anywhere by using the following formula:


=now()

this is a volatile function and should force EXCEL to do a recalculation.

PS I would define a "kludge" as an inelegant fix for a problem , I am not sure of Samt's definition, but I would guess it is something similar.

[crinas]

Thanks. I tried that and it didn't work. What's also weird is that everything is fine when the file is saved on the c drive but when I'm on a network drive, that's when I have problems. Thanks for the definition. I'll just have to work off the c drive for now.

[SamT]

Kludge = A "gitter dun now!" Band Aid until you can fix the problem, also known as a Bondo (http://bondo.com/), because sometimes, it's forever.

[crinas]

Thanks.