Tuesday, February 14, 2012

Conga Composer: Excel repeating formula

In general you can enter a Conga merge formula starting with &=&=.  That makes your formula repeats along with the merge region.  You can also use {r} and {c} to change the references in the formula, so they move along with the merged in data rows instead of staying static.  Detailed information can be found in the documentation.

One interesting thing that's not mentioned in the documentation is the situation of having two merge regions on the same sheet.  I had just that the other day.  A vlookup formula works across the two regions, providing a way to bridge data that's difficult to do directly in Salesforce.  All was well until I saw the results.  The vlookup formula repeated way beyond what I needed it for.  I realized that it's because the {r} in the formula reaches the length of the lookup table_array, instead of the length of the region in which the formula is located.  I moved the two regions around on the sheet to no avail, thinking somehow I can make the Conga rendering engine understand what I need.

After contacting the support, they provided a solution: moving the lookup region to a different sheet.  That did the trick.  Lesson: keep separate things really separate, so the rendering engine doesn't get confused.  Unless an additional sheet is not an option, I think the solution is an adequate one.