Thread: External Linking

Hi, Looking for some help with this. I have a sheet which has the following formula:

=COUNTIF('Jan 10'!$J$2:$J$73,"=1")+COUNTIF('[JAN 10.xls]OTHER GLASGOW WS '!$N$3:$N$112,"=1")-(COUNTIF('Jan 10'!K2:K73,"=1")/2)-(COUNTIF('[JAN 10.xls]OTHER GLASGOW WS '!$O$3:$O$112,"=1")/2)

The cells it links to contain the following:

internal sheet (part of same workbook):

=IF(ISNA(VLOOKUP(E2,$L$2:$M$42,2))=TRUE,0,IF(I2="x",VLOOKUP(E2,$L$2:$M$42,2),0))

External sheet:

=IF(ISNA(VLOOKUP(F3,$P$2:$Q$44,2))=TRUE,0,IF(H3="x",VLOOKUP(F3,$P$2:$Q$44,2),0))

These formulae return a number (based on the Vlookup) or a 0 (depending on true or false). My problem is that when the external sheets are open the first formula (countif etc) works perfectly fine but when these external sheets are not open, the formula returns #VALUE!

I do not understand why this is the case as there is no text to cause this calculation to return #VALUE!

Any help/advice would be much appreciated!

Dave.

Hi there,

The #VALUE! error is likely coming from a limitation with COUNTIF - i.e. it doesn't work when an external workbook that it is referencing is closed. I would suggest using SUMPRODUCT as a workaround. If your not familiar with the function, there is plenty of help on Google.

I would hazard a guess that something like:

=SUMPRODUCT(--('Jan 10'!$J$2:$J$73=1))+SUMPRODUCT(--('[JAN 10.xls]OTHER GLASGOW WS '!$N$3:$N$112=1))-(SUMPRODUCT(--('Jan 10'!K2:K73=1))/2)-(SUMPRODUCT(--('[JAN 10.xls]OTHER GLASGOW WS '!$O$3:$O$112=1))/2)

...would work.

HTH,

SamuelT