About Using Excel Formulas in the Post-Back Process
This topic describes the use of Excel formulas using Dynamic Excel Integration with the Endicia Professional post-back process.
- Important! Dynamic Excel Integration is BETA test only and might not work with various versions of Microsoft Windows and Microsoft Excel.
Connecting to a file through Dynamic Excel Integration allows Endicia Professional to support the use of formulas upon post-back to an open Excel file. Editing a formula is optional. Unless you need to customize the formula, you should keep the formula with its default value. If you need to customize a formula, you can find specific tips for each variable by selecting the variable from the menu on the Post Back Data Map window.
Formulas may include Excel formulas, variables, or both. Endicia Professional replaces variables with the literal value. If you completely remove a formula, the mapped cell will have a blank value upon data post-back. Values that do not start with an equal sign will have a static value upon data post-back. If you want a value to appear, use an equal sign. For example, =1+1 will result in a 2 being placed in the mapped cell.
Some general guidelines and tips include:
- Mapping multiple Endicia Professional fields to the same column will not generate an error but generally will not produce useful results.
- You can use $CurrentExcelRow and $CurrentExcelRow to implement formulas based on the relative position of other cell data.
About Working with Variables and Formulas
The Formula selection menu offers many variables that start with a dollar sign ($). The post-back process replaces these variables with the actual value from Endicia Professional prior to being sent to Excel. For example, upon posting back to Excel, the actual order number for the shipment being processed replaces $OrderNumber.
- Important! Dynamic Excel Integration is BETA test only and might not work with various versions of Microsoft Windows and Microsoft Excel.
When you map an Excel column to a field, Endicia Professional automatically assigns a default formula. You should edit the formula only if you require custom logic, such as an adjustment calculation to a value before posting back to Excel.
On the Post Back Data Map window, the Formula button appears in bold font for any edited formula.
To prevent issues with mathematical formulas, Endicia Professional replaces numeric values with a 0 if the mapped field does not apply to an order. For example, if insurance does not apply to an order, the insurance fee will be 0 rather than a blank.
Removing a formula completely will result in a blank value being posted back to the specified column.
You can apply a static value by entering information that does not include one of the dollar sign values or an equal sign (=).
Assigning static values for Dynamic Excel Integration connections is best done with formulas. There is a place to assign static values in the post-back options that will still work but may have unexpected results when combined with formulas.
Columns in Excel may have formatting applied to them without causing issues. For instance, if a column is formatted as currency it will cause numeric values such as postage amounts to be written back to excel in currency format (e.g. $1.23).
URL's can be written back to Excel that result in clickable hyperlinks. Tracking URL's for the Endicia and USPS websites are already included in the selection menu to allow for easily adding them to a formula. You can add other URL's by entering either with http or https followed by the value (the rest of the URL).
Note that CSV and text files do not retain formatting and lose the ability to click a link after you close that type of file.
It is not necessary to actually include any $ values in an Excel formula. For instance, if the Postage field was assigned to column C but the formula was edited to read, =1+1 column C would always receive a value of 2 for each order. Although this provides a great amount of flexibility for custom reporting, use it with caution because it might confuse future users who attempt to modify the post-back functionality.
Excel Formula Examples
Excel formulas must start with an equal sign. Some common Excel functions and explanations follow.
- Important! Dynamic Excel Integration is BETA test only and might not work with various versions of Microsoft Windows and Microsoft Excel.
A formula for this would be something like: =$Postage*0.1
This formula writes back the 10% of the postage for each order to the mapped column. Drop shippers may need a more complex versionusing a different handling fee depending on the customer. For example, assuming that the X column contained the name of the customer being shipped to. A more complex nested IF formula might be something like: =IF(X$CurrentExcelRow="CompanyA",$Postage*0.1,IF(X$CurrentExcelRow = "CompanyB",$Postage*0.2,0))
This would result in the formula writing back 10% of the postage if the X column for this order indicated “CompanyA”, 20% of the postage if X was “CompanyB” or 0 if X contained neither value.
You can use $CurrentExcelRow to implement formulas based on the relative position of other cell data. For example, you could assign an incremented number to a shipment by using $CurrentExcelRow or perhaps =$CurrentExcelRow-1, which would write back one less than the current row (in the case where the numbering should start at 1 after an initial header row).
Advanced Excel users may want to take advantage of data held in other rows or columns than the one being targeted by Endicia Professional. In the case of columns it is usually best to hard-code the column letter. For example, A$CurrentExcelRow when posting to row 6, would post to Excel in cell A6. However, some users may want to pull data from different rows to include in their formulas. This requires the OFFSET function.
The Offset function begins with a cell reference, then a positive or negative integer indicating how many rows up or down to look for a value, followed by a second value indicating how many columns left or right to look.
For example, the following would look for a value in Cell A1: =OFFSET(B2,-1,-1)
This function can be combined with all manner of logic. The following is an example using some of the preceding formulas to apply a second mathematical function to a value found in another cell: =OFFSET(C$CurrentExcelRow,-1,0)+$Value
Copyright © 2017 Endicia. All Rights Reserved.
Set a Handling Fee...MORE