Cell Referencing in Report Designer

The behaviour of relative and absolute cell references differs when copied and pasted to other cells.

Relative cell references work correctly within the report layout itself, but may not correctly reference the Excel worksheet. After a report is executed, any relative cell references are changed to absolute cell addresses in the worksheet. Therefore, values may no longer be correctly referenced.

We recommend different strategies for referencing cells based on the section of the report layout.

In the header and total sections, use an absolute cell reference that references the worksheet or a named range. Absolute cell addresses remain as the cell address in Excel after the report is executed.

In Report Designer, use the INDIRECT function.

This example calculates the difference between columns B and C for each row in the report.


A	B	C	D
Account code	Amount	Amount	Formula
ABCxxx	123.456	123.456	=INDIRECT("rc[-2]",FALSE)-INDIRECT("rc[-1]",FALSE)

This example calculates the difference between column B and C for each row in the report, and then displays either Negative or Positive.


A	B	C	D
Account code	Amount	Amount	Formula
ABCxxx	123.456	123.456	=IF(INDIRECT("rc[-2]",FALSE)<0,"Negative",IF(INDIRECT("rc[-1]",FALSE)>0,"Positive"))

See support.office.com.