XL: Advanced Filter Criteria Range or List Range Box Empty

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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.

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.

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.

MORE INFORMATION

For more information about using the Advanced Filter with defined names in Microsoft Excel, query on the following words in the Microsoft Knowledge Base:

   advanced and filter and defined and names

REFERENCES

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

   criteria ranges