Microsoft KB Archive/115767

= XL: Advanced Filter Criteria Range or List Range Box Empty =

Article ID: 115767

Article Last Modified on 8/15/2003

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q115767





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.



WORKAROUND
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" 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" 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.

