Solved

Excel workbook won't open when opening a workspace.

Posted on 2001-08-21
5
224 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

828 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