Dynamic Formulas allow you to insert Excel's formulas into cells even when the formula must reference rows that will be inserted later during the merge process. They can repeat for each inserted row or use only the cell where the field is placed.
Dynamic Formulas begin with &== and are followed by an Excel formula.
Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula.
You may use most of Excel’s functions in a Dynamic Formula. The following parameters apply to Dynamic Formulas.
| PARAMETER | DESCRIPTION |
|---|
| {r} | Indicates to use the current row number
Used in conjunction with a column letter to indicate a cell on the current row. When the following is placed in cell C1: &==A{r}*B{r} results in the formula =A1*B1 |
| {integer} | Offset to current row number, where integer is a positive or negative number that represents the number of rows above or below the current row. Used in conjunction with a column letter to indicate a particular cell offset by the number of rows indicated by the integer. {2} would mean to reference a cell two rows below the current row {-1} would mean to reference a cell one row above the current row When placed in cell A10: &==E{1}*D{1} results in the formula =E11*D11 |
Example 1:
Say we’re creating a quote using Opportunities with Opportunity Line Items and we’d like to do some of the math for each Line Item in Excel. (This example is purely academic – Salesforce already computes the product for you.)
We’ll start with a Template Builder window that looks like this:
We’ll create a template that includes a Dynamic Formula to find the product of the price multiplied by the quantity.
The output from this merge would look like this:
In this case, the total does not repeat for each row. If you want it to repeat, use a Repeating Dynamic Formula as shown next.
Example 2:
Let’s extend the previous example by changing the Dynamic Formula to a Repeating Dynamic Formula.
Here’s the modified template:
The output from this merge would look like this:
