Frequently asked questions

Read more about our functions here

=SCOTT. DESC returns GL account description

=SCOTT.GL returns GL account balance

=SCOTT.GLMULTI returns the SUM of multiple GL account balances

=SCOTT.LIST returns a list of parameters used by other functions. Xero lists include accounts and tracking categories. QuickBooks lists include accounts, classes, departments (locations), customers, and vendors

=SCOTT.NGL returns the negative sign of a GL account balance. For folks that like credits to be shown as negative

=SCOTT.RANGE returns sum of account balances

=SCOTT.XTRACK returns GL account balance for a Xero tracking category and option

=SCOTT.XTRACKR returns sum of account balances for a Xero tracking category and option

=SCOTT.XTRACKM returns GL account balance for two Xero tracking categories and options

=SCOTT.XNI Returns the Xero Net Income optionally filtered by one or two tracking categories

=SCOTT.QCLASS Returns GL account balance filtered by a QuickBooks class

=SCOTT.QCLASSR Returns GL account balance for a range of accounts filtered by a QuickBooks class

=SCOTT.QDEPT Returns GL account balance filtered by a QuickBooks department

=SCOTT.QDEPTR Returns GL account balance for a range of accounts filtered by a QuickBooks department

=SCOTT.QCLASS_DEPT Returns GL account balance filtered by a class and a department

=SCOTT.QCUSTOMER Returns GL account balance filtered by a QuickBooks customer

=SCOTT.BUDGET Returns the budget for an account

=SCOTT.QBUDGETALL Returns the budget filtered by a QuickBooks class, a department and a customer

=SCOTT.QALL Returns GL account balance filtered by a class, customer, department, and/or vendor. This function supports a wildcard “*”

=SCOTT.XINVOICE
Returns an array of Invoices for Xero, showing line item detail

=SCOTT.XBILL
Returns an array of Bills for Xero, showing line item detail

=SCOTT.XINVOICE_HEADER
Returns an array of Invoices for Xero, showing only header info

=SCOTT.XBILL_HEADER
Returns an array of Bills for Xero, showing only header info

=SCOTT.XINVOICE_SUM
Returns the sum of selected invoices from Xero

=SCOTT.XBILL_SUM
Returns the sum of selected bills from Xero

=SCOTT.XGLTRX
Returns an array of journals from Xero

=SCOTT.GLMULTITRACK
Returns GL account balance for multiple accounts filtered by a Xero tracking category

=SCOTT.BUDGETMULTI

Returns the budget sum for multiple accounts. This is similar to how the GLMULTI function operates

=SCOTT.QBUDGETALLMULTI - Budget sum for multiple accounts, and allows for selection by Class, Customer, and Department for QuickBooks

=SCOTT.QBUDGETALLR - Budget sum for a range of accounts, and allows for selection by Class, Customer, Department, and Customer for QuickBooks

=SCOTT.BUDGETR - Returns the budget sum for a range of accounts

Scott’s Add-ins is developed to work with desktop and online versions of Excel™.  You will need a current Office 365 subscription to use the add-in.

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, such as =SCOTT.GL, =SCOTT.DESC, =SCOTT.RANGE

The add-in does an initial data build of the organisation’s journal data. This could take a few minutes or a couple of hours, 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.

Yes. Power Query is an excellent tool for selecting, sorting, totaling the data that is generated from our array functions like =SCOTT.XGLTRX, =SCOTT.XINVOICE, and =SCOTT.XBILL. Here is a one minute video that shows you how.

Some company IT departments control which add-ins can be installed to Excel.  We recommend you check with your IT team to verify this is the case.  These are called “managed add-ins”.  Here’s a 60 second video on how this works.

Yes.  This is an Excel feature.  Please go to Excel Preferences / Calculation, and choose “Manual”

No. The data from your accounting system is only refreshed when you click the Recalc button in the add-in.

No. Data only flows one way, from your accounting system to Excel.

Yes! Scott’s Add-Ins subscriptions are per organisation. Just be sure to add the new organization to your subscription. Multiple organisations can be accessed from within the same Excel workbook.

Transaction detail may be accessed by clicking on the drill icon, found in the add-in task pane.

Here’s a 20 second video showing the process.

Or, for Xero transactions, try the =SCOTT.XGLTRX function.

Unlimited users are included with your subscription.  Additional Xero users just need to install the add-in, then connect to Xero. .

Additional QuickBooks users can be added as Delegates. You can find this by clicking on Settings under the Welcome tab.

You are able to connect specific Xero and QuickBooks organizations to specific Excel workbooks.  When the workbook is saved and shared, the workbook will open with only those saved organizations.

When you save, close, and reopen the workbook,  the previously selected organizations will be maintained.  So, when you subsequently share the workbook with a colleague or client, they will only be able to access data from the selected orgs.  

If you do not wish your client or colleague to be able to recalc the workbook, and only see values, please use the VAL button in the taskpane. VAL will convert the workbook to values. Please heed the warning when doing this!

Here is a video showing these options.

Here’s a short video showing the process.

If all cells are returning #VALUE, please try re-installingthe add-in.

If only some cells are returning #VALUE, the 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  Xero Tracking Categories or misspelled QuickBooks parameters such as Class, Customer, that are referenced by =SCOTT functions.

Excel loads the add-in in two parts, the Taskpane and the Custom Functions.
Getting an xldudf in your formula indicates Excel is not able to link the names to the custom functions.  Most commonly, the source of this problem is the custom functions portion is not loaded correctly.

There are a couple of troubleshooting steps to try when this happens:
Firstly, load up a new, empty document, login to Scott’s Add-ins and see if you can run a custom function.
If that works, then go back to your sheet and manually change xldudfSCOTT_DESC to SCOTT.DESC.
Excel should do this automatically, but it appears it is not.

If you cannot run a custom function in a new sheet, then the installation is not working.  We advise removing the add-in, restarting Excel, then re-installing the add-in.  This should clear out any caching issues, and hopefully get you working again.

#BUSY tells the user that Excel is currently recalculating the spreadsheet. Recalculation times are a function of spreadsheet complexity, the amount of =SCOTT functions used in the sheet, and Internet bandwidth. The functions that work the hardest, and take the most time to process, are RANGE and QNI and XNI functions.  Please use these judiciously.

Another possible cause of #BUSY is a Microsoft 365 update.  Please try re-installing the add-in.

Yes.  For more details please see this post

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 your accounting system.  Bank accounts are Balance Sheet accounts which calculate their balances by summing transactions from the beginning of time.  You can read more here.

When specifying a QuickBooks sub-account using the Account Name (not an account number), and having multiple sub-accounts that have the same name, use this format that begins with the main account, then sub_account1, sub_account 2, etc.

 Services:Job Materials:Decks and Patios.

Example:  =SCOTT.GL(OrgID, Services:Job Materials:Decks and Patios, Beg Date, End Date)

Please note that this rule only applies when constructing formulas using the Account Name, not the Account Number.

Wildcards are supported for the =SCOTT.QALL function only.  More info here

To add a new org, remove an org, cancel your subscription, or update your billing info, please click on Settings from the Welcome tab in the add-in task pane. Note: you will need to be connected to your accounting system.