Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Excel workbook won't open when opening a workspace.

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
rosethorn111199
Asked:
rosethorn111199
  • 2
  • 2
1 Solution
 
criCommented:
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
 
rosethorn111199Author Commented:
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
 
criCommented:
And why a C ?
0
 
MoondancerCommented:
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
 
rosethorn111199Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now