Excel: Database Functions Return #REF!

Last reviewed: November 29, 1994
Article ID: Q32741

SUMMARY

When using the database functions DAVERAGE(), DCOUNT(), DMAX(), DMIN(), DSTDEV(), DSUM() and DVAR(), the database function, as well as the defined database and criteria, must all be on the same worksheet or macro sheet in versions 1.50 and earlier. If the database or criteria is on a separate worksheet, the formula will return #REF!.

In Microsoft Excel versions 4.0, 3.0 and 2.2, however, the database and criteria can reside on external worksheets. To calculate the database functions from an external sheet, the database and criteria arguments must contain the path to the worksheets containing the database and criteria.

For example, if the database is defined on "DatabaseSheet" and the criteria is defined on "CriteriaSheet", the following DSUM() formula can be used to sum the "Amount" field:

   =DSUM(DatabaseSheet!Database,"Amount",CriteriaSheet!Criteria)

KBCategory: kbusage
KBSubcategory:
Additional words: 2.2 2.20 3.0 3.00 4.0 4.00 macrosheet

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.