Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Bug???? (Excel Links Not Updating - HELP) 1

Status
Not open for further replies.

Chunkus

Technical User
Jun 12, 2003
24
GB
Hi

Summary of problem:
I have a workbook, that links to another workbook.
The links works fine, when both workbooks are open - but fails to work properly when the linked workbook is closed????

Why would a link only work when the linked file is open????

Detail of my problem:
The formula I am using is a 'dynamic sum', controlled by cell B1 in the host workbook. Cell B1 has a value of 1,2,3 or 4. The value of which controls the sum range:
Value 1: Sum(A1:A1) of the linked file
Value 2: Sum(A1:B1) of the linked file
Value 3: Sum(A1:C1) of the linked file
Value 4: Sum(A1:D1) of the linked file

The Formula being used is as follows:
Sum('[File1.xls]Sheet1'!A1:index('[File1.xls]Sheet1'!A1:A4,1,B1)

The formula works perfectly when both workbooks are open, but not when the linked file is closed.

The really weird part is that the formula still partially works when the linked file is closed, BUT only when Cell B1 has value of 1 or 2. Not 3 or 4!!!!! [mad] [mad] [mad] [mad] [mad]

I've been using Excel for years, and never had a links problem like this. Is it a bug???? [ponder]

Jas
 
TYPO error on the thread

Index range is A1:D1 (Not A1:A4)

Jas
 
Isolating the index function states that the problem lies in the reference part of the function (the same example as yours, but with the function =index('[File1.xls]Sheet1'!A1:D4,1,B1) is a success). I'm about 99% sure that the problem lies within the programming of the function.

In this particular case, you can always use

=choose(B1,'[File1.xls]Sheet1'!A1,'[File1.xls]Sheet1'!A2,'[File1.xls]Sheet1'!A3,'[File1.xls]Sheet1'!A4)


But perhaps it was the reason you wanted explained, not how to work around it...


// Patrik
 
Thanks for confirming the Error Patrik.
[2thumbsup][2thumbsup][2thumbsup][2thumbsup][2thumbsup][2thumbsup][2thumbsup][2thumbsup][2thumbsup][2thumbsup]

It was the error I was interested in (rather than a work around), but thanks for the workaround too.

Jason
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top