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.
A. 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”. More here.
A. Yes. This is an Excel feature. Please go to Excel Preferences / Calculation, and choose “Manual”
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.
Here’s a 20 second video showing the process.
Q. How do I add more users to my subscription?
A. Unlimited users are included with your subscription. Additional Xero users just need to install the add-in, then connect to Xero. Here’s a 40 second video showing the process.
Additional QuickBooks users, please see this blog post.
Q. Can I share my Excel workbook with others who do not have access to my accounting system?
A. If you save the sheet while it is connected to your accounting system, the sheet should retain the connection and allow a user to recalc the sheet. However, at some point the token that relates to the sheet will expire, preventing a recalc. You can experiment with this by sending the sheet to yourself, and opening it . The token should remain in place for 30 days and possibly longer.
Q. How do I update my subscription payment details and subscription type?
A. Here’s a 20 second video showing the process.
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 or _xlduf in one or more of my cells.
A.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 _xldudf_SCOTT_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.
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, 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 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.
Q. How do I pull in the balance for my Retained Earnings account?
A. The add-in cannot pull in all transactions / balance for the Retained Earnings account., because Xero and QuickBooks do not provide all Retained Earnings transaction detail via their API, so the add-in cannot calculate the balance.
Q. I am using QuickBooks sub-accounts and getting #VALUE
A. 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.