Formulas and external references

Hey All,

I am constructing a workbook that is linked to various other workbooks on our network and have come across a particular issue regarding the external references

I enter the following formula into a cell:

=COUNTIF('G:\FolderName\[Orders.xls]Sheet1'!$AJ:$AJ,"WEB")/D21

and the result is #VALUE!

Further to this the formula in cell D21 refers to the same workbook (but is just a plain count function)

=COUNT('G:\FolderName\[Orders.xls]Sheet1'!$B:$B)

and does NOT return an error but the result of the formula

ALSO......when I open the other workbook the errors change to results of formulas (weird???)

I have updated the links when I open the workbook and I see there is no reason why a result would be achieved from one formula and not the other (except perhaps that the workbook needs to be opened for the formula to calculate - which is really silly)

Thanks

DW

With more investigation I have found that this error only occurs with any formula that has a condition in it (ie countif, sumif, vlookup, etc etc) BUT NOT WITH COUNT, COUNTA, SUM etc

Is there any way to amend this through VBA or whatever

TA

DW

thanks for that guys

The only way around it is to have another sheet setup in the workbook that contains the formulas and link to each cell in the external workbook, then refer the formulas to this other sheet

formula->external workbook

formula->sheet ->external workbook

Thanks a bundle

That explains a lot

DW