Referencing Cell Data Across Sheets

Expected Effect

When creating general reports, you often need to add multiple sheet tabs to view different data through switching the tabs.

For example, there is sheet1 containing a data table, you can create sheet2 in which a column chart can be created based on the calculation results from the table in sheet1. The following figure shows the effect.

Implementation Method

When referencing data from other sheets, the format of the formula should be 'sheet name'!cell. For example, 'sheet1'!A1 represents referencing the data of cell A1 in sheet1.

To reference an area of continuous cells, the formula should be 'sheet1'!A1:A3.

Template Preparation

This article uses the completed template of First General Report as the basis for modification.

Open the built-in template in the designer: %FR_HOME%\webroot\WEB-INF\reportlets\demo\NewbieGuide\FirstReport.cpt, or click to download the template First General Report.cpt and open it in the designer.

The following figure shows the effect.

Add a Sheet

At the bottom of the report page, click the icon to add a sheet, right-click the sheet name and rename the two sheets as Sales and Column Chart respectively, as shown in the following figure.

Chart Design

The data source of the original chart in the template is set to cell data, so you can directly select the original chart, then cut and paste it into the Column Chart sheet, as shown in the following figure.

Now preview the report, and you can see that the Column Chart on the second page has no data, as shown in the following figure. .

Next, modify the data of Column Chart.

Select Column Chart and choose Cell Element > Data on the attribute panel. In this example, you simply need to add 'Sales'!  in front of the original cell references. The data is as follows:

• Category Name: ='Sales'!B4, which refers to referencing cell B4 in the Sales sheet.

• Series Name: ='Sales'!C3, which refers to referencing cell C3 in the Sales sheet.

• Value: ='Sales'!C4, which refers to referencing cell C4 in the Sales sheet.

Effect Display

PC

Save the report and click Data Analysis Preview. The sheet names can be seen at the bottom of the page. The preview effect is as shown in Expected Effect.

Mobile Terminal

The template can be previewed on both the DataAnalyst app and the HTML5 terminal. The following figure shows the effect.

For details, you can click to download the template Referencing Cell Data Across Sheets.cpt.