2

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?

Improve this question
1
  • 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, 2013 at 21:54

4 Answers 4

Reset to default
5

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.

Improve this answer
1
  • And here's the same in Excel 2003 for completeness.
    – DMA57361
    Jun 16, 2011 at 8:05
1

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.

Improve this answer
1
  • Thanks - just double checked and there is no VBA code in this spreadsheet at all.
    – Anonymous
    May 26, 2011 at 17:09
1

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.

Improve this answer
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .