Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel workbook won't open when opening a workspace.

Posted on 2001-08-21
5
Medium Priority
?
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Accepted Solution

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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.

670 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