Microsoft KB Archive/110211

Microsoft Knowledge Base

XL: Advanced Filter Does Not Retain Defined Names

Last reviewed: March 27, 1997
Article ID: Q110211

5.00 5.00c 7.00 | 5.00

WINDOWS         | MACINTOSH

kbusage

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for the Macintosh, version 5.0

SUMMARY

In Microsoft Excel, names or formulas that you enter into the List Range, Criteria Range, and Copy To boxes in the Advanced Filter dialog box are not retained when you exit the dialog box. Instead, they are converted into absolute cell references.

Advanced Filter will automatically use certain names if they are defined in your workbook. Advanced Filter recognizes and will use the global names Database, Criteria, and Extract, and the local names <Sheetname>!Database, <Sheetname>!Criteria, and <Sheetname>!Extract, if they are defined in your workbook.

MORE INFORMATION

When you use the Advanced Filter dialog box in Microsoft Excel, there are three edit boxes present:

   List Range       Equivalent to the Database defined name in earlier
                    versions of Microsoft Excel.

   Criteria Range   Equivalent to the Criteria defined name in earlier
                    versions of Microsoft Excel.

   Copy To          Equivalent to the Extract defined name in previous
                    versions of Microsoft Excel. The Copy To box is
                    available only if the "Copy to Another Location"
                    button is selected.

If you enter a name (for example, Database , Criteria , or Extract ) or a formula (for example, =INDIRECT("Criteria")) into any of the edit boxes in the Advanced Filter dialog box, the names or formulas will be replaced with their absolute cell references when you exit the dialog box.

For example, assume your worksheet has Database defined as $A$1:$C$20, Criteria defined as $E$1:$G$2, and Extract defined as $E$4:$G$4. If you enter those names into the appropriate boxes in the Advanced Filter dialog box, the next time you enter the dialog box you will see $A$1:$C$20, $E$1:$G$2, and $E$4:$G$4, not Database, Criteria, and Extract.

If you define the global names Database, Criteria, or Extract in your workbook, the absolute definitions of those names will be used automatically when you go into the Advanced Filter dialog box.

REFERENCES

"User's Guide," version 5.0, chapter 21

KBCategory: kbusage KBSubcategory: Additional reference words: 5.00 5.00c 7.00 Keywords : kbusage Version : 5.00 5.00c 7.00 | 5.00 Platform : MACINTOSH WINDOWS 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: March 27, 1997 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.