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))),"")}