We are pleased to announce the release of a new function:
= SCOTT.XTRACKM
For those of you that are new to these pages, Scott’s Add-in for Excel and Xero, is a series of new, custom Excel functions that enable users to dynamically sum up Xero general ledger transactions into a single Excel cell.
This new function builds on the capabilities to report on Xero journal lines that have Tracking Categories / Options assigned to them. Our venerable function, = SCOTT.XTRACK , allows the user to select ONE Tracking Category and Option. The function returns the balance of all transactions that have been coded to that Tracking Category / Option.
However, Xero allows for up to two Tracking Category / Options to be assigned to a journal line. For users of Xero that have exhausted the reporting capabilities associated with multiple Tracking Categories, help is here! Take the example of a company that uses (2) Tracking Categories, Region and Product Class to categorize journal entries.
= SCOTT.XTRACKM
allows the user to select journal lines that have BOTH of the Tracking Categories / Options you specify. Here’s the syntax:
= SCOTT.XTRACKM
(Organisation ID,Account Code,Tracking Category 1, Tracking Option 1, Tracking Category 2, Tracking Option 2, Start Date, End Date)
This function will return the sum of all journal lines that have BOTH of the Tracking Categories assigned to them. Journal lines that meet only one of the Tracking Category criteria will not be used in the calculation. You would of course use the = SCOTT.XTRACK for this purpose.
More tips on how to use Scott’s Add-in for Xero Tracking Categories:
=SCOTT.XTRACKM(org id, account code, category 1, option 1, category 2, option 2, beginning date, ending date)
will show all transactions that have BOTH of the specified categories .
If we omit category 2,
(org id, account code, category 1, option 1,,, beginning date, ending date)
the function will return “0”. BOTH tracking categories must be specified
use =SCOTT.XTRACK for finding transactions that meet have ONE Tracking Category assigned
If we omit BOTH tracking categories
(org id, account code,,,,, beginning date, ending date)
the function will find all transactions with NO tracking code assigned. This is the same behavior found by using =SCOTT.XTRACK
*same rules apply for =SCOTT.XTRACKR
To your success,
Scott