When you want to do a complicated calculation and get it into a document... and into a customers hands do you want to keep the end result? This could be a total, average, year to date… really anything on the right side of the equal sign after the left did all the work. It's not new information Excel can do some very impressive calculations. Did you know you can push those values from Excel and into Word, Powerpoint, PDF or even to Salesforce itself?
While reading this I am assuming you already know how to tag a document. If not refer to below…
Let's use a simple use case. You are generating a quote/proposal and you have some type of finance calculation. For example maybe you have a competitive price calculator. Whatever calculation... the method is the same.
What are we trying to avoid?
Press button - Get Document - Manually Edit & Calculate it till finished - Send it out.
How does Excel effect above?
Press button - Magic Happens - Get Final Version & Deliver.
No need for the user to have to edit the document in the middle of the process when you take this path. So what do you need to do this?
Let's start with the calculation. You have to have everything on the left side of the equal sign to create the right… right? Whether it's static or dynamic data from Salesforce this can be done.
In the example above I have two values I want to calculate in my output. Starting Price & Advanced Pricing. In my use case I do not want to show the work/math of how I got there. I just want to show the total. To do this I simply hide the “how” part in another sheet.
In the picture above you can see the formula for Starting Price is looking up a named range in Excel called EM_Starting_Price. If I navigate to the range I have it hidden in a sheet called Advanced Pricing.
In the Formula value for starting price you will see a simple calculation. I am taking the Opportunity Amount value and the Opportunity Price Guide value and multiplying them. This is simple for demo but add a more complex calculation and the trick is the same. Another thing to note is the range value for the starting price…
“EM_Starting_Price”
Best practice alert…
I made that tag/value up. It was not in the field tagger. Tags typically are named object name underscore field name with brackets around it…
<<Object_Field>>
I do not have an object in my data model called “EM”. So why did I use it? This is the trick. Tags like this that are not in the tagger can use used in a way where Excel is the data source and not just Salesforce. For best practice I use EM meaning Excel as Middleware so when I go to edit this document later I know how to reverse engineer what I did. I know it came from Excel. Again this is not required syntax.
In the picture above Im just now mapping that value to the output sheet of the excel document. But we are not limited here. If I am using this as a data source I could push this value to other templates like word or even back to Salesforce.
Reference below to better use excel as a data source and push those values into documents.
Help Documentation on Connected Data
Now do we get those values back into Salesforce at the same time? First make sure you have the data model to support receiving this calculation. For example I have a field on my opportunity called Starting Price.
Next you want to create an insert-update. More info on details of the setup below.
The trick here is in the true value put in the tag and make sure to add brackets. See below.
Make sure in the setup for the insert update the checkbox for running at the beginning is not checked. This will allow the calculation to run and pass back to Salesforce.
Thanks for reading!
—Kind Regards
Dave
