Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Someone has sent me a spreadsheet (as an email attachment) where a simple sum formula has not been recalculated, as when I open the email from Outlook, it opens in Excel 2010 in protected view and I can see the cell value is wrong.

However when I enable editing, the formula is recalculated and the correct cell value shown. My question is - how have they managed to send me an email attachment with the formula not recalculated, but it recalcs when I open it? If they had manual recalculation set in the spreadsheet when they sent it, shouldnt it still be manual when I open it and not recalc?

Any thoughts?

share|improve this question

migrated from stackoverflow.com May 26 '11 at 21:05

This question came from our site for professional and enthusiast programmers.

    
This seems really odd. If it was set to Manual calculation, it wouldn't calculate upon enabling editing. The only possibility I can think of is that it is a file with complex calculations - but automatic calculation - but the user got impatient during the calculation and pressed ESC and then saved it, ignoring the warning. Unlikely though... :-/ –  Peter Albert Feb 14 '13 at 21:54
add comment

4 Answers

I believe protected view does not allow formula calculation.

MS suggests this method of disabling protected view:

http://office.microsoft.com/en-us/excel-help/what-is-protected-view-HA010355931.aspx#BM5

  1. Click the File tab. The Microsoft Office Backstage view appears.
  2. In the Backstage view, under Help, click Options. The Options dialog box appears.
  3. Click Trust Center, and then Trust Center Settings.
  4. Click Protected View.
  5. Make selections that you want.

Try unchecking the box pertaining to Outlook email attachments.

share|improve this answer
add comment

In the Excel options (2007 at least, I would expect similar in 2010) is this section:

enter image description here

It is entirely possible that they have gone into the options and set Excel to manual recalculation. This is the most likely thing that I can think of that would cause what you are seeing.

-=EDIT=-

Or maybe not, that setting seems to be saved per workbook, in 2007 at least, maybe they changed in in 2010, or it was not saved in the workbook in earlier versions. Knowing what version it was made in could be important, I would not be surprised if this setting was global (saved per-system rather than per-workbook) in 2003 or earlier versions.

Perhaps whatever change he made prior to saving simply did not trigger an automatic recalculation for whatever reason, too minor a change, or the change was done in VBA (I do not know if VBA would trigger a recalc) or it was quite simply something to do with the phase of the moon, a freak-occurence if you will.

share|improve this answer
    
And here's the same in Excel 2003 for completeness. –  DMA57361 Jun 16 '11 at 8:05
add comment

Which format is the file sent to you ? xlsx, xlsm or else ?

Can you reproduce the behavior if you first save the file on your hard drive ?

What kind of formula does not recalc ? and to which cells does it refer to ?

In the first I would have first thought like Steve that there were vba in the Workbook_open. Maybe they customized the XML inside the xlsx ? but i don't know if you can change Calculation mode with it.

share|improve this answer
add comment

The only thing I can think of is that the Workbook_Open() event is programatically turning off automatic calculation for them, but not for you. Perhaps it is checking that a file exists, and if it does, it turns off calculation. Open the VBA Editor, double click the ThisWorkbook item in the Project viewer and look for the Workbook_Open subroutine.

share|improve this answer
    
Thanks - just double checked and there is no VBA code in this spreadsheet at all. –  Anonymous May 26 '11 at 17:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.