This article introduces the procedure for setting formulas in Excel template files with Office File Creator.
Points when setting formulas
Formulas can be used as they are in Excel, but the following points should be considered when setting formulas.
- Formulas that specify a range, such as the total value of child records, should be specified as "child record first row cell:child record first row cell" (e.g., A1:A1). When outputting, the range is changed to "A1:A3" according to the number of records in the child records.
- If the formula refers to a cell in the child record row and there are no records in the child record, the formula is output as "=0".
- After entering the formula, please set the formatting again. After entering a formula in a cell, the formatting may automatically change to general.
- Merged fields cannot be set within a formula.
NG example: ="{!Opportunity.Amount}"+"{!Opportunity.Tax__c}"
Formula Setting
As an example, set up a formula in the invoice template file created with Generate an Excel file in Salesforce using Office File Creator – (1) Create a template file. Set the formulas in the yellow filled areas of the template file.
First, set up a price × quantity formula for the Subtotal of the Opportunity Product. The formula "=F9*G9" is set, and an error "#VALUE!" is displayed because of the string calculation, since F9 and G9 are set to merged fields. The output will show the result of the calculation between the price and quantity values, so proceed as is. If "#VALUE!" is displayed in the same way as below, proceed as is.
Next, set the formula for the Subtotal of Opportunity. Set the formula "=SUM(H9:H9)". For example, if the number of child records is 5, the formula "=SUM(H9:H13)" will be output.
Next, set the formula for 10% Sales Tax. Set the formula "=H10*0.1".
Next, set the formula for the Total. Set the formula "=H10+H11".
Next, set the formula for the Total. Set the formula "=H12".
Finally, select the cell with the formula and set the format to Currency. Entering a formula in a cell may cause the format to automatically change to general. Re-set the format after entering the formula.
Formula setup is complete.
Output file. Click "Enable Editing" to enter edit mode.
*Files downloaded from the Internet are in protected view, so the formula recalculation results will not be displayed when the file is opened.
The recalculated formula results are displayed.
If there are no child records, formulas that refer to child records are output with "=0".
The template file created in this article can be downloaded below.
Invoice_Excel_Template_(formula ver).xlsx
