Excel in Drawloop

Owned by Jodie Miners
Last updated: May 06, 2016
2 min read

As well as creating typical Mail Merge Word documents, both Drawloop - (formerly Nintex Document Generation) and Conga can create Excel documents. Conga seems to have far more Excel capability. When I do an Excel merge in Conga I will write that up. 

The process of setting up Excel is quite different than Word. See the very basic Drawloop help documentation. 

As the Drawloop Help documentation is not going to help you much, here is some tips. 

  • I could NOT for the life of me work out how to get an image from a Salesforce record working in Excel, so I gave up. 

Formulas

  • Formulas are problematic. This help document is ALL you are going to get on Excel functions - it's not good at all. I had to go to Drawloop support for the most basic function help.  
  • So, you enter your formula, then click on the cell you want to reference in the formula. The formula is then going to use the Cell Name rather than the reference. You need to DELETE the Cell Name and enter the A2 style Cell Reference instead. 

Repeating Rows

  • Make extensive use of the INDIRECT Excel function. Eg, I have my repeating rows Name in cell B2:J2. In column A I want to number the rows that are returned. 
  • In A2 enter: =INDIRECT("A"&ROW()-1)+1. When Row 2 repeats it will also repeat this formula and therefore fill down create a numbered list. Thanks to Drawloop support for this tip. 

Totals on Repeating Rows

  • To total a column after repeating rows enter =SUM(C2:INDIRECT("C"&ROW()-1)) in C3 if your repeating row name is in A2:C2. Thanks to Drawloop support for this tip. 
  • The URL is not going to come out of Salesforce in a clickable format, and you probably want to use the Hyperlink function to make it pretty. So return the URL from Salesforce into a cell, and hide that column. Then add a new column that uses the Hyperlink function. 
  • Eg if your URL is in H2 and your displayed column is in J2 then enter =HYPERLINK(INDIRECT("H"&ROW()),"Click Here")

  • And you may want to wrap that in an IF formula to only display the hyperlink if a URL is returned into column H. 

  • NOTE: Hyperlinks with Indirect functions are not clickable and visible until you click Enable Editing after you open it. So I'm not sure how you can use this in an Excel spreadsheet that is then delivered as PDF. This is how they are shown before enabling editing. 

 

 

So it looks like there are lots of handy tips the Drawloop support team can share, but it's a matter of individually asking them about every single question you have, rather than them sharing it in their knowledge base. 

 

Â