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:


    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)


    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)



  • what are U counting in D21?
    I only get a result for numbers, any text is ignored.
    Your countif formula seems to be OK, i.e. the syntax works with my sample data

    Regards, Manfred

  • 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



  • 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


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!