XL: Advanced Filter Criteria Range or List Range Box Empty

ID: Q115767

The information in this article applies to:

SYMPTOMS

In Microsoft Excel, when you use the Advanced Filter to filter a list on a worksheet, and the criteria range is located on another worksheet, the Criteria Range box is empty when you use the Advanced Filter again on the worksheet that contains the list. Additionally, the List Range box is empty when you use the Advanced Filter again on the worksheet that contains the criteria range.


CAUSE

This behavior occurs because the criteria range that you use to filter your list is defined on the worksheet that it is contained in. Similarly, the list range only appears in the Advanced Filter dialog box if it is contained on the current sheet.

If you use a criteria range that is contained on the same worksheet as the list, and you select a cell in the list before you use the Advanced Filter, both the list range and the criteria range are displayed in the Advanced Filter dialog box.

WORKAROUNDS

To automatically display the criteria range in the Advanced Filter dialog box, when the criteria range is contained on a different worksheet but in the same workbook as your list, follow these steps:

  1. Select the worksheet that contains the criteria range.


  2. On the Insert menu, click Name, and then click Define.


  3. If you have previously used this range as a criteria range, delete the local name "SheetName!criteria."


  4. In the Names In Workbook box, type "Criteria" (without the quotation marks) Click the Refers To Box, and select the criteria range on the worksheet. Click OK.


To automatically display the list range in the Advanced Filter dialog box, even when the selected cell is not contained in the list, follow these steps:

  1. Select the list range.


  2. On the Insert menu, click Name, and then click Define. In the Names In Workbook box, type "Database" (without the quotation marks) and click OK.


NOTE: The list range is only displayed in the Advanced Filter dialog box if the worksheet that contains the list is active when you use the Advanced Filter.

If the list range and the criteria range are contained in different workbooks, you must enter the missing criteria range or list range by typing the range, or by clicking in the range box, and selecting the worksheet that contains the respective range.


REFERENCES

For more information about Setting Up A Criteria Range, click the Search button in Help and type:

criteria ranges

Additional query words: 5.00a 5.00c 7.00a 


Keywords          : kbdta xllist 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS 
Issue type        : kbprb

Last Reviewed: July 29, 1999