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

[Chunkus]

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(A11) 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!!!!!

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

Jas

 

[Chunkus]

TYPO error on the thread

Index range is A11 (Not A1:A4)

Jas

 

[01Patrik]

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'!A14,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

 

[Chunkus]

Thanks for confirming the Error Patrik.


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

Jason