Microsoft KB Archive/182736

= XL98: Formula Errors Appear After Updating PivotTable =

Article ID: 182736

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q182736



SYMPTOMS
When you update a PivotTable, formulas that refer to a date in the PivotTable return an error value.



CAUSE
This problem occurs when all of the following conditions are true:
 * You open a workbook that was created in an earlier version of Microsoft Excel. -and-


 * The workbook includes a PivotTable that contains dates. -and-


 * You use formulas that explicitly refer to those dates.

For additional information about using dates as text in PivotTables, please see the following article in the Microsoft Knowledge Base:

109420 XL: Dates in Pivot Table May Be Converted into Text



WORKAROUND
To work around this behavior, edit the formula so that it refers to the date as a date value instead of a text value. For example, retype the following lookup formula

=VLOOKUP("7/25/59",$D$1:$G$10,4)

as follows:

=VLOOKUP(DateValue("7/25/59"),$D$1:$G$10,4)



MORE INFORMATION
In earlier versions of Microsoft Excel, dates in a PivotTable are automatically formatted as text. This means that if you reformat a cell that contains a date with a number format, the format in the cell is reset to a text number format when you update the PivotTable. However, Microsoft Excel 98 allows you to use rich text formatting in a PivotTable, including dates.

Microsoft Excel 98 automatically formats any date in a PivotTable with a date format. This is an enhanced formatting feature and is different from earlier versions of Microsoft Excel. Therefore, a formula that refers to a date that uses the text format in a PivotTable returns an incorrect result or an error value, such as the #N/A or #VALUE! value.

Additional query words: XL98 pivot table break calculation err upgrade

Keywords: kbbug kbpending KB182736

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.