Excel workbook won't open when opening a workspace.

Posted on 2001-08-21
Medium Priority
Last Modified: 2006-11-17
I have a multi-workbook workspace.  There are many references across books and everything works fine except for the following formula.

=+"'[Growth Factor Input.xls]Growth Factors Monthly Terminal'!$a$9:$c"&TEXT(COUNT('[Growth Factor Input.xls]Growth Factors Monthly Terminal'!C:C)+8,0)

As you can see the formula evaluates to a cell range in the workbook Growth Factor Input.xls.

The result is then used via the INDIRECT function as follows.

=VLOOKUP(B$6,INDIRECT('[Control.xls]Growth Factor Limits'!$B$6),3)

When the workspace opens, this book is included in the workspace but Excel stops here and shows an Open File dialogue box to select the correct file.

I believe the reason for this is the default location of workbooks, currently c:\My Documents.

First I could change this default location to point to the correct place, but this is a development system and the final location is not known.  I don't want to have to change the default file open location for all computers that use this workspace since that is not practical.

I can understand why Excel stops here (besides the fact the M$ is stupid), but what can I do about it?

Question by:rosethorn111199
  • 2
  • 2
LVL 13

Accepted Solution

cri earned 400 total points
ID: 6412697
Is the workbook 'Growth Factor Input.xls' open when you try to access it ?

And, you seem to be passing an range with your formula, therefore you must use an array formula (mark suitable range, paste your formula and make sure you close with Ctrl+Shift+Enter)

Probably not the cause, but I would not use spaces in workbook names, rename it to Growth_Factor_Input.xls

Author Comment

ID: 6413310
You asked the right question so I am awarding the points.

No that workbook was not opened.

So I re-arranged the workspace so that workbook opened first.  Then it was available to all others.

The problem has been solved.

LVL 13

Expert Comment

ID: 6414563
And why a C ?

Expert Comment

ID: 6418483
I agree, why a "C" grade?  I can fix this for you!

Please check this link, it explains the grading process and impact quite nicely.  Personally speaking, I am both a Moderator as well as a participating Expert and I always say Thanks with an "A" grade, since if I remain clear and active in my questions, I always achieve excellent results.

How Are Expert Points determined? (Points offered remain the same, but grades awarded impact the expert score).

Each time a member asks a question, they assign it a specific number of question points, depending on its difficulty. If an Expert answers the question, and the asker accepts the answer, the Expert is rewarded a certain number of Expert Points, depending on the grade given to the answer. Your Expert Points are calculated based on the number of points assigned to a question times the grade received, with A=4, B=3, C=2, D=1, and a Reject=0.


A member assigns 50 question points to a question, and they rate an Expert's answer to the question with a 3 (a B grade).

The Expert who answered the question will receive 150 Expert Points for their accepted answer, calculated by multiplying the 50 question points to the answerer's grade of 3.


Community Support Moderator @ Experts Exchange

Author Comment

ID: 6420413
I assigned a C since the question was not answered directly, but given the scoring system can agree with a change to an A.

Sorry about that.


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I came across an unsolved Outlook issue and here is my solution.
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question