Transaction Spill PRO
The following functions hot link a list of GL Journals by GL Code, Invoices by Contact Code or Stock Item Lines by Stock Item Code for a single company, in one call spilling out into adjacent cells.
You will get Excel error "#SPILL!" if the function has not got enough space to write to adjacent cells.
The Wizards "View GL Journals List", "View Invoice List" and "Stock Items Sold List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
Get Journals By GL Code
Show Journals for GL 200
Click on any CXL figures in the sheet and selecting "Trans" menu will show the same Journals
And allow you to drill down to any Journal Detail
Performance Rated:A+
Example
| Get Journals for GL/Account "200": | |
| Journals list from year 2026: |
=CXL.GLJournalsSpill( "COMP_A", "200", 2026 )
By default returns 1 year for financial year 2026 |
| only 24 periods back: | =CXL.GLJournalsSpill( "COMP_A", "200", 2026, 0, "", "", 24 ) |
| restrict to last 50 Journals in 2026: | =CXL.GLJournalsSpill( "COMP_A", "200", 2026, 0, "", "", 0, 50 ) |
| And by Analysis Codes: | |
| by Rep MARK: |
=CXL.GLJournalsSpill( "COMP_A", "200", 2026, "MARK" )
Where MARK is Tracking 1 or Class code |
| by Area NORTH: |
=CXL.GLJournalsSpill( "COMP_A", "200", 2026, "", "NORTH" )
Where NORTH is Tracking 2 or Location code |
Wizard: "View GL Journals List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
Parameters:
| Parameter | Description |
|---|---|
| CompCode |
Single Company code Short code assigned by Consolidated XL, or by you to identify your company |
| GLcode/ItemCode/ContactCode |
GL Code, Stock Code or Contact code to get anything after "," is ignored ContactTransSpill() - "Receivable" or "Payable" to get outstanding transactions |
| [UptoYear]/[UptoPeriod]/[NoPeriods] |
from UptoYear, get transactions for NoPeriods NoPeriods default=12, Max=12 (1 year) |
| [CC_Track1_Class]/ [Dep_Track2_Loc] |
Optional - Filter by Cost Centre/Department AKA as Tracking codes in Xero and Classes/Locations in QuickBooks |
| [Options] |
Optional - Comma delimited list "AddTitle" add title on first line "NoHeading" removes column headings For GL's GLJournalsSpill() "ShowTax" Adds Tax info |
| [FromYear]/[FromPeriod] |
Specify the point in time to Query |
| [Limit] |
Restrict number of lines returned to x Default/Max=5000 |
| [ExtraFilter] |
Optional - Extra Filter Value to filter by |
Get Invoices and lines By Contact Code
Show Invoices for Accounts "PC001"
Optionally show the Invoice lines
Click on any CXL figures in the sheet and selecting "Trans" menu will show the same Transactions
And allow you to drill down to any Transaction Detail
Performance Rated:A+
Example
| Get for QuickBooks Vendor transactions with display name "Vendor1", Xero Contact "Vendor1": | |
| Outstanding Transaction list: | =CXL.ContactTransSpill( "COMP_A", "Vendor1", 0 ) |
| Transaction list from year 2026: |
=CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026 )
By default returns 5 years of data from financial year 2026 |
| only 24 periods back: | =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 24 ) |
| restrict to last 50 transactions: | =CXL.ContactTransSpill( "COMP_A", "Vendor1", 2026, 0, 0, 50 ) |
| All Outstanding Transactions | |
| All Debts/Receivables: | =CXL.ContactTransSpill( "COMP_A", "Receivable" ) |
| All Creditors/Payable: | =CXL.ContactTransSpill( "COMP_A", "Payable" ) |
Wizard: "View Invoice List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
Parameters:
| Parameter | Description |
|---|---|
| CompCode |
Single Company code Short code assigned by Consolidated XL, or by you to identify your company |
| ContactCode |
Xero Contact code, QuickBooks Display Name to get anything after "," is ignored if "Receivable" or "Payable" get all outstanding transactions |
| [UptoYear]/[UptoPeriod]/[NoPeriods] |
from UptoYear, get transactions for NoPeriods NoPeriods default=60, Max=60 (5 years) |
| [CC_Track1_Class]/ [Dep_Track2_Loc] |
Optional - Filter by Cost Centre/Department AKA as Tracking codes in Xero and Classes/Locations in QuickBooks |
| [Options] |
Optional - Comma delimited list "AddTitle" add title on first line "NoHeading" removes column headings For Invoices ContactTransSpill() "ShowTax" Adds Tax info "ShowCurrency" Adds Currency info Note: Base figures are valued at transaction original exchange Rate "ShowLines" Adds Lines info |
| [FromYear]/[FromPeriod] |
Specify the point in time to Query |
| [Limit] |
Limit List to x records (5000 is default and max) |
| [ExtraFilter] |
Optional - Extra Filter Value to filter by |
Get Stock Lines By Stock Code
Show Stock Movements for "BOOK"
Click on any CXL figures in the sheet and selecting "Trans" menu will show the same Transactions
And allow you to drill down to any Transaction Detail
Performance Rated:A+
Example
| Get for Stock/Product lines: | |
| Lines from year 2026: |
=CXL.StockLinesSpill( "COMP_A", "PROD01", 2026 )
By default returns last 6 periods of data from financial year 2026 |
| only 24 periods back: | =CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, 0, "", "", 24 ) |
| restrict to last 50 lines: | =CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, 0, "", "", 0, 50 ) |
| And by Analysis Codes: | |
| by Rep MARK: |
=CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, "MARK" )
Where MARK is Tracking 1 or Class code |
| by Area NORTH: |
=CXL.StockLinesSpill( "COMP_A", "PROD01", 2026, "", "NORTH" )
Where NORTH is Tracking 2 or Location code |
Wizard: "View Item Sold List" shows how to use the functions below. It's best to create a sheet using the wizard, to understand the use and possibilities.
Parameters:
| Parameter | Description |
|---|---|
| CompCode |
Single Company code Short code assigned by Consolidated XL, or by you to identify your company |
| GLcode/ItemCode/ContactCode |
GL Code, Stock Code or Contact code to get anything after "," is ignored ContactTransSpill() - "Receivable" or "Payable" to get outstanding transactions |
| [UptoYear]/[UptoPeriod]/[NoPeriods] |
from UptoYear, get transactions for NoPeriods NoPeriods default=6, Max=12 (1 year) |
| [CC_Track1_Class]/ [Dep_Track2_Loc] |
Optional - Filter by Cost Centre/Department AKA as Tracking codes in Xero and Classes/Locations in QuickBooks |
| [Options] |
Optional - Comma delimited list "AddTitle" add title on first line "NoHeading" removes column headings For Stock Lines StockLinesSpill() "ShowCurrency" Adds Currency info |
| [FromYear]/[FromPeriod] |
Specify the point in time to Query |
| [Limit] |
Restrict number of lines returned to x Default/Max=5000 |
| [ExtraFilter] |
Optional - Extra Filter Value to filter by |