Let Us Break it Down for You

The functions explained

=SCOTT. DESC    returns GL account description

=SCOTT.GL        returns GL account balance

=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.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.QBUDGET Returns the budget for an QuickBooks account

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

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.  You will need a current Office 365 subscription to use the add-in.

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

Q: What happens when I connect the add-in for the first time to a new QuickBooks or 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 accounting data when I add a new, non-Scott’s function to a cell, such as =SUM?

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

Q: Does Scott’s Add-Ins write-back, or update the QuickBooks or Xero database?

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

Q. Can I use multiple QuickBooks or 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 QuickBooks or Xero organisation you wish to attach to Excel.  Multiple organisations can be accessed from within the same Excel workbook.

Q. How can I see the transactions that comprise the balance I am seeing in a cell?

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

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. I am seeing a #NAME error in one or more of my cells.

A. A #NAME error results when the add-in is not loaded.  This can be a bit confusing, in that the add-in may still show in Excel.  However, if you try to access the functions, and do not see =SCOTT functions, the add-in is not loaded.  We recommend un-installing the add-in, then re-installing.

The most common reason for the add-in not loading, is a problem with your computer environment.  Please make sure you have the latest Office 365 update installed, and that your IT department has not implemented a policy that prevents the add-in from loading.

Q. I am seeing a #BUSY error in one or more of my cells.

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

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 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.

Menu