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 !Database, !Criteria, and !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.