Cross-Sheet Linkage and Data Entry

Application Scenario

By methods such as parameters and hyperlinks, data (values) can be transferred across reports, but cannot be transferred across multiple sheets in the same template.

So how to transfer a value in a certain cell from one sheet to another sheet? Namely, how to obtain data across sheets? This document introduces the solution in detail.

In the example shown in the following figure, Order Cost in sheet1 is obtained by calculating cell values from sheet2. When you change relevant values in sheet2 by data entry, the value of Order Cost in sheet1 changes accordingly.

Implementation Method

You can use the formula =sheetname!cellname to obtain values across sheets. For example, if you want to transfer the value from cell A2 in sheet2 into a cell in sheet1, you can add the formula =sheet2!A2.

If the sheet name contains special characters such as hyphens and underscores, you need to enclose the sheet name with single quotes. That is to say, the formula should be written as ='sheetname'!cellname.

Data Preparation

1. First, create the dataset ds1 for sheet1 and dataset ds2 for sheet2 with the following SQL statements.

ds1: SELECT * FROM ORDERS_old

ds2: SELECT * FROM ORDERSDETAIL_old

2. Add a template parameter and set the default value to 10001, as shown in the following figure.

3. Select Drop-down Box Widget as the widget, and select the ORDERID field from the ORDERS_old table in Data Dictionary as Actual Value and Display Value separately to bind the widget to the parameter on the parameter panel.

Sheet 2 Preparation

1. Create sheet2. Design the table in sheet2, drag the fields from ds2 into the corresponding cells, and add a filter condition for cell A2 to bind the template parameter to cell A2, as shown in the following figure.

Add the formula SUM(C2*D2) to cell E2, add a drop-down box widget for cell A2, and add file widgets for cells B2 to D2, as shown in the following figure.

2. Set data entry attributes for sheet2, as shown in the following figure.

Cross-Sheet Linkage

1. Design the table in sheet1, drag the fields from ds1 into the corresponding cells, and add a filter condition for cell A2 to bind the template parameter to cell A2, as shown in the following figure.

 Add the formula sheet2!E2+B2 to cell C2. With this formula, the system summarizes the value of cell E2 in sheet2 and the value of cell B2 in sheet1.

2. Set data entry attributes for sheet1, as shown in the following figure.

Effect Display

Save the template and select Data Entry Preview. If you modify the cell values in sheet2, the value of Order Cost in sheet1 will change accordingly. The effect is the same as that shown in section "Application Scenario."

Note:

The effect cannot be previewed on mobile terminals.

The calculation logics within a sheet and across sheets are as follows:

1. Data within a sheet is calculated from left to right and from top to bottom.

2. Data across sheets is calculated from left to right. (If any data is referenced from other sheets, the referenced data is calculated first.)

Problem

Data may fail to be retrieved during circular cross-sheet data retrieval. For example:

1. In sheet1, cell C1 contains the formula sheet2!D1, cell C2 contains the number 3, and cell C3 contains the formula sheet3!E1, as shown in the following figure.

2. In sheet2, cell D1 contains the formula sheet1!C2-sheet1!C3, as shown in the following figure.

2. In sheet3, cell E1 contains the number 2, as shown in the following figure.

The following figure shows the preview effect.

Cause

First, during sheet1 loading, cell C1, which should have been calculated first, is stopped from being calculated because the formula in cell C1 references the value from sheet2. In this case, the values in sheet2 are calculated, during which cell D1, which references the values of cells C2 and C3 in sheet1, is calculated first. A value (the number 3) exists in cell C2 of sheet1. The formula in cell C3 references the value from sheet3. However, sheet3 is not triggered to be calculated in this case. Therefore, an empty value is returned. This is because a sheet that has triggered one referenced sheet to be calculated once cannot trigger another one to be calculated again.

Implementation Method

You can place all the cells to be referenced into one sheet where other sheets reference the values of these cells.

1. In sheet1, cell C1 contains the formula sheet2!D1, cell C2 contains the number 3, and cell C3 contains the formula sheet2!E1, as shown in the following figure.

2. In sheet2, cell D1 contains the formula sheet1!C2-sheet1!C3, and cell E1 contains the number 2, as shown in the following figure.

3. The following figure shows the effect.

For details, you can download the template Cross-Sheet Linkage.cpt.