Question

Dynamic Template Side Formulas within the Same Axis as Replicated Rows

  • 11 October 2023
  • 1 reply
  • 66 views
C
Badge +2

How would I go about doing a table something like this:
 ------------------------------------------------------------------------------------------------------------------------------------
|Component A | Component B | Component C | Component Total | Rate         | Final 
|------------------------------------------------------------------------------------------------------------------------------------|
|SFFieldA          | SFFieldB          | SFFieldC         | Sum(A,B,C)           | SFFieldD  |  Sum * SFFieldD |
|------------------------------------------------------------------------------------------------------------------------------------|

Where SFFields are some field tagged into the template from our Apex Data Sources within the row which is tagged to be replicated, so that when the user opens excel sheets and modifies field A, B, or C, that the Sum will update as well as the final.

Any formulaic reference to a cell (direct or indirect) to excel cells that are within a replicated row within that same row will cause DocGen to fail with a Microsoft Office error (so a sum of all rows along a column works, but a sum within the same row will fail). Seems like somewhat of an essential functionality, but have been unable to find anything that works, any questions on the forums or any solutions in the documentation.

1 reply

AnthonyD
Userlevel 2
Badge +6

Hello @Caleb Quick ,

To clarify, you are saying if you are having issues with performing formulas in the same repeating rows.

I myself use a different approach using the excel as middleware
 

I would populate the data into a worksheet, then have another worksheet where I can update the output of the information (sums, calculations, trims, etc) and then finally allow this ‘updated worksheet’ to be presented for use in the word document.


For your use case, perhaps, all you need to do is populate the data in a hidden worksheet and and then format the data in visible worksheet.

Lastly, be sure use references such as “HIDDENWorksheet!$A1:$A:100” and not “HIDDENWorksheet!$A”. Column references do not work, they must be cell references.

I hope this helps,

Anthony

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings