Index formula with closed workbooks

[mharder]

I have an index formula that works with the source workbook open but produces a #value! error when source workbook closed.
I see the complete network path to the source workbook in the formula while it is closed.
In other cells vlookup formula's continue to work with the same source workbook.

I am trying to produce a multiple corresponding list from a single value where the output is from a column to the left of the value.

I copied the formula with the source workbook open just to save space.

{=IF(ROW(ebts.csv!$A2)-ROW(ebts.csv!$A$1)>COUNTIF(ebts.csv!$D:$D,$B$3),"",INDEX(ebts.csv!$A:$A,SMALL(IF(ebts.csv!$D$1:$D$1500=$B$3,ROW(ebts.csv!$D$1:$D$1500)),ROW(ebts.csv!$A2)-ROW(ebts.csv!$A$1))))}

Additional cells below just change the ROW reference from
{=IF(ROW(ebts.csv!$A2... to
{=IF(ROW(ebts.csv!$A3... and so on

Thanks in advance

 

[Aladin Akyurek]

What is the first cell in which you invoke the formula in question?

 

[mharder]

My formula is from cell B5. The source workbook has column's A-E with a header row.

 

[Aladin Akyurek]

My formula is from cell B5. The source workbook has column's A-E with a header row.

B4:

=SUMPRODUCT(--(ebts.csv!$D$1:$D$1500=$B$3))

B5:

Control+shift+enter...

=IF(ROWS($B$5:B5)<=$B$4,
   INDEX(ebts.csv!$A$1:$A$1500,
    SMALL(IF(ebts.csv!$D$1:$D$1500=$B$3,
        ROW(ebts.csv!$D$1:$D$1500)-ROW(ebts.csv!$D$1)+1),
      ROWS($B$5:B5))),
   "")

 

[mharder]

Thanks so much. You are now my newest hero.
Would you offer an explaination to the error in my formula??

 

[Aladin Akyurek]

Thanks so much. You are now my newest hero.
Would you offer an explaination to the error in my formula??

You are welcome.

Reading off range objects from a closed book, leads to array objects. COUNTIF does not work with array objects, hence the error value. SumProduct, on the other hand, is designed to crunch arrays.

For example:

A1: 2
A2: 3

[book.xls]Sheet1!A1:A2 becomes {2;3} if book.xls is not open.

While not an error-producing bit, I also replaced ROW(Y)-ROW(X) to a ROWS() expression that, moreover, does not refer to the external book. The replacing expression is processed much faster.