SAP Knowledge Base Article - Public

2983082 - It is not possible to use absolute cell reference inside the crosstab in Analysis Office

Symptom

In Analysis Office, when trying to set an absolute cell reference in a formula using the F4 key, the reference is converted back to relative.

For example: $B$4 will turn into B4 after pressing "Enter" or after leaving the cell.

Environment

SAP BusinessObjects Analysis, edition for Microsoft Office

Reproducing the Issue

  1. Add a new column or line in Analysis Office via New Lines;
  2. Try to enter a formula using absolute cell reference inside the Analysis Office grid/crosstab;
    screen1.png 
  3. Confirm the formula will change back to relative cell reference;
    screen2.png

Cause

The tool is working as designed. This is not a bug in the tool.

References within the grid cannot be absolute Excel addresses for AO because if you change the navigation state of the crosstab it will mostly produce incorrect results. Therefore, AO will always use relative references.

Resolution

To resolve the situation, you can create the formula outside the grid/crosstab using absolute cell references, pointing to the cells inside the crosstab as references.

Using this method, it will be executed as an absolute reference, but will always point to the correct field, even if the reference crosstab is changed. That is, outside crosstab the absolute references will work normally.

Keywords

Absolute reference, F4 formula cells, formula cells, formula converter, new line, dollar sign , KBA , BI-RA-AO-XLA , Excel Addin , Problem

Product

SAP Analysis for Microsoft Office all versions