Solved

Excel workbook won't open when opening a workspace.

Posted on 2001-08-21
5
214 Views
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?

Cheers
rosethorn
0
Comment
Question by:rosethorn111199
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
cri earned 200 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
0
 
LVL 1

Author Comment

by:rosethorn111199
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.

Thanks
rosethorn
0
 
LVL 13

Expert Comment

by:cri
ID: 6414563
And why a C ?
0
 
LVL 1

Expert Comment

by:Moondancer
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.

Example:

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.

http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Moondancer
Community Support Moderator @ Experts Exchange
0
 
LVL 1

Author Comment

by:rosethorn111199
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.

Cheers
rosethorn
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now