How to waste an hour or two in Excel....

[jmw]

I am working on an upgrade of a spreadsheet to introduce a new correction.
I first opened a spreadsheet from my collaborator. I ran some calculations and it was fine; he uses solver.

When I then opened my own spreadsheet I noticed that it appeared quite happy working out one set of calculations but if I inverted the calculations, all I got was the last answer displayed. I tried F9. No good.

(in fact, because I save with some data already populated, all I was looking a were the saved answers from the last time I saved the workbook, and was not seeing live calculations; that's one reason why I wasted so much time)

Note getting the right answers caused me some concern and I unprotected everything, un-hid some sheets and began to trace formulae through.
I even rewrote a few sample cellls and changed some cell references.
No Luck.
I couldn't understand it.
I closed my spreadsheet and opened up a copy from another source.
Same problem.

Finally and by good luck, I found the problem.
Excel options settings are not all of them specific to the workbook.
I was fooled because I don't show sheet tabs or column and row headers.
His does.
So in his spreadsheet I could see row and column headers and sheet tabs but in mine I could not.

But, he uses solver and I use iterations.

Opening his spreadsheet first he has iteration switched off.
Mine usually opens with the saved options which include iteration switched on.
If I select Iteration in my spreadsheet options, it affects both workbooks.

Now why oh why does Excel treat some options as workbook specific and others not?
And why does it not tell you this is what it does?

Is it usual behaviour for Excel that some options can be workbook specific and others not?
Is there something I should be doing when I protect the sheet and workbook to protect the options for my workbook?


JMW

http://www.ViscoAnalyser.com

 

[electricpete]

I don't totally follow the lesson.

I get the fact that some options are saved with a workbook and some options are saved with the excel program installation.

Are you saying that opening up someone else's workbook changed your excel settings such that when you opened up your own workbook it acted differently?

Was their vba code in the other person's workbook?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[jmw]

Not a lesson, an observation that Excel has caught me out unexpectedly and a quest for enlightenment.

In Excel, under tools/options/calculation, in mine I check Iteration, I set and iteration limit and I select Automatic calculation.

In tools/options/view I un-check view sheet tabs and I un-check view columns and row headers.

In my collaborators spreadsheet he has a different set of options selected so that iteration is not checked but view sheet tabs and view row and column headers is checked.

When you save the spreadsheet, it apparently retains the options settings.

When I open his first, his selections are naturally those that apply to his sheets.
Then when I then open mine, my spreadsheet retains my view options but not my calculation options.

I get his iteration check box settings and he appears to get my iteration limit settings and we each get our own view options.

In his worksheet I see my iteration limits but his unchecked iteration check box.

In which ever sheet I check the iteration check box or un-check it, it applies to both workbooks or to all open workbooks.
Whatever view settings there are remain as selected by the author for that spreadsheet. Changing the view options in one spreadsheet does not affect the view options in another workbook open at the same time.

I am curious to know if this is an inadvertent error in Excel, a deliberate construction and if it is one over which I can have some control.

I don't see any means to use sheet protection or workbook protection to protect all of these options and nothing that relates to calculation options.



JMW

http://www.ViscoAnalyser.com

 

[GregLocock]

The first worksheet you open in an instance of excel is used to define the iterations setting.



Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[jmw]

Thanks Greg, that's what I appear to have discovered the hard way, but why?
What else is there in the settings that follows this pattern and where is it documented and why don't we have an option to protect these settings in our workbooks?
What happens when I close Excel and then re-open it with a blank spreadsheet? what are the default settings?
I guess I'll have to experiment.

JMW

http://www.ViscoAnalyser.com