×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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
www.ViscoAnalyser.com
 

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

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.

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

(OP)
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
www.ViscoAnalyser.com
 

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

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

 

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

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

(OP)
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
www.ViscoAnalyser.com
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources