Excel file becomes inconsistent with Sum in Pivot Table from a column with Formula

[KageGarden]

Hi,

A Pivot Table uses a reference to a field from the main table as VALUES field, and the referenced cell contains a formula. When the data is filled from SAP, and user tries to open the table, there is an error message about inconsistency and the need to restore Excel file.

The error doesn't happen:

1. if there is no formula in a field referenced from Pivot Table.
2. if there is a reference to Count of VALUES field instead of Sum of VALUES field in a Pivot Table.
3. if there is an update delay, so the user need to click twice to update Pivot Table.

Steps for reproduction;

1. Upload a template with error (ZXXT_demo_022_pivot_template_error.xlsx) via SMW0 into ZXTT_DEMO package and overwrite the template with Object Name = ZXTT_DEMO_022-XLSX. This template uses standard Flight Model data.
2. Generate Flight Model data via transaction BC_DATA_GEN if Flight Model tables are empty. A minimum data volume is sufficient.
3. Run transaction Z_XTT_DEMO and select Demo Example = 022 "Merging cells/Flight Model". Specify minimum row count (5-10 rows).
4. Download Excel file and try to open it. You will get an error message about inconsistent Excel file which needs to be restored. The example of such file is attached too (file ZXXT_demo_022_pivot_error_repro_data.xlsx).
5. Check in the template that the Pivot Table in "Pivot" list reference DIVIDE column from "Table" list with a formula in DIVIDE cell.
6. You can load the second attached template without an error (file ZXXT_demo_022_pivot_template_noerror.xlsx) and repeat steps 1-4. You will see that there won't be inconsistency error. This is despite the fact that a Pivot Table on "Pivot" list is identical to the template with error and references the same DIVIDE field with the same formula. This is probably because the update of Pivot Table is "delayed": you will need to refresh Pivot Table twice to fill it with the data. You can also check the data in a file "ZXXT_demo_022_pivot_noerror_repro_data.xlsx
7. The files are attached:
   ZXXT_demo_022_pivot_noerror_repro_data.xlsx
   ZXXT_demo_022_pivot_template_noerror.xlsx
   ZXXT_demo_022_pivot_error_repro_data.xlsx
   ZXXT_demo_022_pivot_template_error.xlsx
   "

[bizhuka]

Hi
Thank you to find the issue

This is probably because the update of Pivot Table is "delayed"

Nope, replaced fm with VLOOKUP, the error is present

you will need to refresh Pivot Table twice to fill it with the data

just make pivot options like that

Cannot find the difference between templates
I'll try to compare XML files, the description of Excel is not sufficient