Result from Workbook
A powerful feature of workbooks is that they provide hundreds of formula/functions.
With complex formula rules, it may be preferable to express them in a workbook separate from the report so that they can be used in every report without replication. The Result from Workbook function is provided to copy parameters from the report to a separate workbook where complex formulas operate on the data and return those results to the report.
In the separate workbook, named ranges are used to identify where up to 6 parameters are copied from the report as well as where the results are located that are copied back to the report. Parameters are designated with the names Parameter1 to Parameter6, and the results are designated with the name Results.
 |
Note that each parameter as well as Results can refer to either a single cell or a range and that the Scope of each name must be set to WORKBOOK.
For more information on named ranges and cells see the Named Cells/Ranges section of the Template Studio document under the DESIGN category in the Document Library.
For example, in water treatment, a lookup table is used to calculate giardia levels from chlorine, temperature and pH. These tables are readily available from the EPA web site.
 |
To make this suitable as a lookup to a report, each of the inputs and the output need to be identified. The inputs (yellow) are given the names Parameter1, Parameter2 and Parameter3. The output cell is given the name Results.
The formula for the output is similar to the following which uses the temperature, chlorine, and pH to determine the result from the EPA lookup table:
=VLOOKUP(C6, C8:AH425, MATCH(D6, C8:AH8,1))
Note that this workbook is provided in the XLReporter\_library\_Import directory as giardia-lookup.xlsx
Example
As a simple example, use an external workbook to sum Parameter1 and Parameter2.
 |
This configuration uses a Calculators workbook that has a Results, Parameter1, and Parameter2 Named Cells with the following declarations:
 |
The Results cell contains a =Parameter1+Parameter2 formula.
 |
Use the settings:
Apply To | |
|---|---|
Cell | $B$3:$D$3 |
Direction | Down |
End | All cells are empty |
Placement | |
|---|---|
Cell | $F$3 |
Type | Insert At End |
Direction | Down |
Setting | Value |
|---|---|
Workbook Name | Calculator |
Parameter 1 | $C$3 |
Parameter 2 | $D$3 |
Parameter 3 | |
Parameter 4 | |
Parameter 5 | |
Parameter 6 | |
Paste | Values |
Settings | |
|---|---|
Workbook Name | The name of the workbook which contains the rules that determine the Results. This can be fixed text or a cell reference. If the full path of the workbook is not specified, it is assumed that the workbook is in the Input folder of the project. If the file extension of the workbook is not specified, it is assumed to be the default set in the project. |
Parameter 1 – Parameter 6 | Up to 6 parameters can be passed to the workbook. The values in these parameters are written into the named cells Parameter1 – Parameter6 in the workbook specified. These can be hard coded or cell references. If set to a cell reference and the Apply To range spans multiple rows, the row for the cell reference is adjusted as each row in the Apply To range is processed. |
Paste | This setting determines what from the Results is copied back to the report. To use the formatting from the workbook, select Values and Formats, otherwise select Values to use the formatting set in the template. |