XL2000: Advanced Filter Does Not Detect Additions to List

View products that this article applies to.

This article was previously published under Q213996

SYMPTOMS

When you filter a list using the Advanced Filter command, Microsoft Excel 2000 does not detect new additions to the list.

Back to the top

CAUSE

The Advanced Filter command in Microsoft Excel initially recognizes your list, but does not detect additions to the list.

For example, if you have the following information in a worksheet

   A1: Region  B1: Sales   C1:     D1:
   A2: East    B2: 200     C2:     D2: =B2>200
   A3: West    B3: 500     C3:     D3:

when you click Advanced Filter on the Data menu, the List range box displays the following range:

$A$1:$B$3

When you filter the list using the criteria in D2, and then add the following information

  A4: South    B4: 600
  A5: North    B5: 700

when you click Advanced Filter on the Data menu again, the List Range box still displays the original range:

$A$1:$B$3

Back to the top

WORKAROUND

Update the List range box. To do this, use either of the following methods.

Back to the top

Reset the List

You can reset the list by turning AutoFilter and Advanced Filter on and off:

Select a cell in the original list range.
On the Data menu, point to Filter, and then click AutoFilter.
On the Data menu, point to Filter, and then click AutoFilter. Excel removes the AutoFilter.
On the Data menu, point to Filter and then click Advanced Filter.

Back to the top

Edit the List Range Box

Select a cell in the original list range.
On the Data menu, point to Filter, and then click Advanced Filter.
Type or select the new list range in the List range box. Click OK.

Back to the top

Microsoft Excel 2000 automatically recognizes your list as a database. Because of this behavior, it is not necessary to define the range for your database. Functions such as sorting, subtotaling, or filtering use the list as a database even though it is not defined as a database.

Back to the top

REFERENCES

For more information about the Advanced Filter feature, click Microsoft Excel Help on the Help menu, type advanced filter in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Back to the top