Problem with formula/links

[rac1974]

Hi all,

The following formula is only working if I have the source spreadsheet (aaa.xls) open. If it is not open, then I am getting #VALUE!
The main issue is that cell B11 changes quite often. As soon as I change this cell I get the error and have to open the source spreadsheet again. Any clues as it's a real pain

Thanks in advance


{=IF(ROWS(A$105:A105)<=COUNTIF('G:\Data\[aaa.xls]aaa'!$C$2:$C$5000,$B$11),INDEX('G:\Data\[aaa.xls]aaa'!$B$2:$B$5000,SMALL(IF('G:\Data\[aaa.xls]aaa'!$C$2:$C$5000=$B$11,ROW('G:\Data\[aaa.xls]aaa'!$C$2:$C$5000)-ROW('G:\Data\[aaa.xls]aaa'!$C$2)+1),ROWS(A$105:A105))),"")}

[daddylonglegs]

I don't believe COUNTIF will work with a closed workbook, change to SUM, i.e.

=IF(ROWS(A$105:A105)<=SUM(('G:\Data\[aaa.xls]aaa'!$C$2:$C$5000=$B$11)+0),INDEX('G:\Data\[aaa.xls]aaa'!$B$2:$B$5000,SMALL(IF('G:\Data\[aaa.xls]aaa'!$C$2:$C$5000=$B$11,ROW('G:\Data\[aaa.xls]aaa'!$C$2:$C$5000)-ROW('G:\Data\[aaa.xls]aaa'!$C$2)+1),ROWS(A$105:A105))),"")

[rac1974]

thanks for the tip, unfortunately it didn't work - it returned blanks. This probably suggests that you are right when you sat COUNTIF does not work frim a closed workbook.

Any other suggestions out there?

[daddylonglegs]

thanks for the tip, unfortunately it didn't work - it returned blanks. This probably suggests that you are right when you sat COUNTIF does not work frim a closed workbook.

I can't see any way it would return blanks if your original formula didn't, it's not materially different from yours, just doing the COUNTIF a slightly different way.

It still need to be confirmed with CTRL+SHIFT+ENTER as your original was