XL5: Extracted Data Not Automatically Updated in Formulas

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you use the Advanced Filter to extract data, and you choose to copy the data to another location, any formulas that reference the extracted data range will not immediately reflect the changes caused by the extracted data (even if the calculation method is set to automatic). However, the formulas will be updated the next time the worksheet is recalculated.

WORKAROUNDS

In order to update formulas referencing the extracted data, the worksheet must be recalculated. To work around this situation, follow the appropriate procedure below:

• To manually recalculate your worksheet, press F9.

  -or-

• If you are using the AdvancedFilter method in a Visual Basic, Applications Edition, macro, add the following line to the AdvancedFilter statement:

        ActiveSheet.Calculate
  

  -or-

• If you are using a Microsoft Excel 4.0 macro, add the CALCULATE.NOW() function to the macro to force the worksheet to recalculate.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c for Windows.