The functions explained
=SCOTT. XDESC returns GL account description
=SCOTT.XGL returns GL account balance
=SCOTT.XRANGE returns sum of account balances
=SCOTT.XTRACK returns GL account balance for a tracking category and option
=SCOTT.XTRACKR returns sum of account balances for a tracking category and option
=SCOTT.XTRACKM returns GL account balance for two tracking categories and options
Q. What version of Excel™ do I need to use Scott’s Add-Ins?
A. Scott’s Add-ins is developed to work with desktop and online versions of Excel™. If using Excel™ for desktop, verify your system is running a compatible version before subscribing. Minimum compatible versions are:
• Office on Windows (version 1904 or later, connected to Office 365 subscription)
• Office on Mac (version 16.24 or later, connected to Office 365 subscription).
Q. What’s an add-in?
A. An add-in for Excel™, is additional functionality that is added to your “out of the box” Excel™. In this case, you will be adding the custom functions that we have developed. The functions currently available are =XGL, =XRANGE, =XDESC, =XTRACK. =XTRACKR, =XTRACKM .
Q: What happens when I connect the add-in for the first time to a new Xero organisation?
A: The add-in does an initial data build of the organisation’s journal data. This could take a few seconds or a few minutes, depending on the volume of transactions for the organisation. You will receive an email, alerting you that build has finished, and you can begin to use the add-in.Note: this is a onetime process. Subsequent recalculations of the sheet do not require another “build”. These recalculations will happen very quickly.
Q: Does the add-in recalculate and pull fresh Xero data when I add a new, non-Scott’s function to a cell, such as =SUM?
A: No. The data from Xero is only refreshed when you click the Recalc button in the add-in.
Q: Does Scott’s Add-Ins write-back, or update the Xero database?
A: No. Data only flows one way, from Xero to Excel.
Q. Can I use multiple Xero™ organisations with the add-in?
A. Yes! Scott’s Add-Ins subscriptions are per organisation. Just be sure to purchase a subscription for each Xero™ organisation you wish to attach to Excel™. Multiple Xero organisations can be accessed from within the same Excel workbook.
Q. I am seeing a #VALUE error in one or more cells in my spreadsheet.
A. #VALUE errors result when Excel cannot interpret the data in your formula. Be sure that you are using cell references (ex. B1,C3) for your formula, NOT actual values (ex. 4000, 01/10/2020).
Another common reason for #VALUE errors is from misspelled Tracking Categories / Options that are referenced by =SCOTT functions.
Q. Does the add-in work with Xero ledger accounts affected by multi-currency transactions?
A. The add-in works well with Income Statement accounts affected by multi-currency, but has some limitations for Balance Sheet accounts. A full discussion here.
Q. How do I pull in balances for my Bank accounts?
A. First, be sure that you have assigned an account code to your Bank account. Then be sure to use a Start Date equal to or earlier than the date of your first transaction in Xero. Bank accounts are Balance Sheet accounts which calculate their balances by summing transactions from the beginning of time. You can read more here.