EBMS Excel Add-In Troubleshooting

The Excel add-in is a powerful tool that allows the user to use MS Excel tools to calculate data accessed directly from EBMS.   This EBMS option is designed for users who have experience entering formulas within MS Excel.   The following steps may assist you in troubleshooting a user's spreadsheet or give the user some tips in using the Excel add-in effectively::

Troubleshooting tips:

  1. Test the calculated values: Verify a new spreadsheet or dashboard by comparing the calculated totals with a report, totals displayed in EBMS, or other method of validating totals.  

  2. Start Simple:  Many formulas are nested with optional parameters to accomplish the desired result.   Replace nested formulas with constants, remove ranges, or simplify variables to troubleshoot a possible issue.   Start simple and add the variables, parameters, and nested functions after testing each additional factor.  

  3. Copy nested functions to a blank cell:  A -460 database error indicates a query error from a probably nested function.  Insert the nested function such as an EBMSQuery function into a blank cell in the spreadsheet and evaluate the results.  Example: =EbmsQuery("Account>='50000-000' .and. account <='59999-999' .and. '-"&LEFT(A29,3)&"' $ ACCOUNT")  will return text that is prefixed with %Q or similar characters.   The additional text can be evaluated to identify syntax errors.

  4. Solicit help from online spreadsheet samples or from  a Microsoft Excel expert within the office if the user does not have extensive MS Excel experience.  Eagle Business software may have some sample spreadsheets available but offers limited MS Excel training.  

Common issues:

  1. Result does not show properly:  Prefix the root EBMS function with the '=' sign to communicate the result of the function into the specific spreadsheet cell:  Example: =EBMSHistoryTotal( "GL.Difference", EbmsRange("50000-000", "59999-999", 2019, 4, 2019, 6) places the numeric result into the cell

  2. Result does not show properly:  Format the Excel spreadsheet cell to match the function result.     The Catagory General can be used for both text and number results.