Stott’s Add-ins have announced the release of a new function: =SCOTT.XTRACKM
If you’re not familiar with Scott’s Add-in for Excel and Xero, it 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:
(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.
To find out more about Scott’s-Add-ins, check out their directory listing.