From Add-in Formulas
The From Add-in Formulas function is used to import data from formulas provided by 3rd party add-ins in Excel. This allows reports to be more “portable” as now the data from these add-in formulas can be viewed by anyone as there is no more add-in dependency.
Settings | |
|---|---|
Add-in | If the Add-in providing the formulas is an xla or xlam file, specify the name of it for this setting (including the file extension). |
Remove Formulas | Typically, the add-in formulas should be removed in the final report. To do so, set this to either All or Range. All removes every formula on the report worksheet while Range removes the formulas specifically from the Range specified. |
Range | If Remove Formulas is set to Range, the cell reference specified for this setting will have all formulas removed so that just the values remain. Alternatively, this can be set to the start of an array formula reference anywhere in the workbook. When set to this, any range of cells containing this formula reference is removed leaving only the values behind. Additionally, if the Add-in specified is for the PI DataLink from OSIsoft (Pidldialogs), any range of array formulas is triggered to resize before the formulas are removed. In order for this to work the PIdldialogs add-in must installed in Excel. To install, in Excel select File, Options. In Excel Options select Add-ins on the left. On the right, click the Go button for Manage. If PidlDialogs is listed, make sure it is checked. If it is not listed, click the Browse button to add it. If the PI server has been installed by a third-party like Rockwell Software or Emerson Automation Solutions browse to the installation folder for the historian of that software and then to PIPC\Excel and select pidldialogs.xla. If the PI server has been installed directly from OSIsoft, locate the folder where PI DataLink is installed, browse to the Excel folder beneath it and select pidialogs.xla. Make sure PIdldialogs is checked and click OK to install. Note, in order for this function to work, a licensed copy of Microsoft Excel must be installed on the system. |
Example
A report template is configured with multiple sets of formulas from the PI DataLink add-in across multiple worksheets. All the formulas use timestamps written to cells $A$1 and $A$2 which are configured as Variable connections in the template. The time duration varies, and the formulas must be removed so that the report can be viewed on systems where the PI DataLink add-in is not installed.
To accomplish this, a From Add-in Formulas function is configured. The settings are:
Setting | Value |
|---|---|
Add-in | pidldialogs.xla |
Remove Formulas | Range |
Range | =PI |
With these settings, every range containing an array formula starting with =PI is refreshed and then the formulas removed leaving just the values behind.