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

Get a Balance sheet, with Cr,db and Value columns, using excel form Xero or Quickbooks

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

Syntax: =CXL.GLJournalsSpill( CompCode, GLcode, [UptoYear], [UptoPeriod], [CostCentre], [Department], [NoPeriods], [Limit], [Options] )
Performance Rated:A+

Get Invoices and lines By Contact Code

Get a Balance sheet, with Cr,db and Value columns, using excel form Xero or Quickbooks

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

Syntax: =CXL.ContactTransSpill( CompCode, ContactCode, [UptoYear], [UptoPeriod], [NoPeriods], [Limit], [Options] , [ExtraFilter] )
Performance Rated:A+

Get Stock Lines By Stock Code

Get a Balance sheet, with Cr,db and Value columns, using excel form Xero or Quickbooks

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

Syntax: =CXL.StockLinesSpill( CompCode, ItemCode, [UptoYear], [UptoPeriod], [CostCentre], [Department], [NoPeriods], [Limit], [Options], [ExtraFilter] )
Performance Rated:A+

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
[UptoYear]/[UptoPeriod]/[NoPeriods] By NoPeriods default=6, Max=12
Go back from NoPeriods to UptoYear/UptoPeriod (0 for all upto a limit of 5000)
[CostCentre]/[Department] Optional - Filter by Cost Centre/Department
AKA as Tracking codes in Xero and Classes/Locations in QuickBooks
[Limit] Default=10 (Max = 5000)
[Options] Optional - Comma delimited list
"AddTitle" add title on first line
"NoHeading" removes column headings

For GL's GLJournalsSpill()
"ShowTax" Adds Tax info

For Invoices ContactTransSpill()
"ShowTax" Adds Tax info
"ShowCurrency" Adds Currency info
Note: Base figures are valued at transaction orginal exchange Rate "ShowLines" Adds Lines info

For Stock Lines StockLinesSpill()
"ShowCurrency" Adds Currency info
[FromYear]/[FromPeriod] Specify the point in time to Query
[Limit] Limit List to x records (500 is max)
[ExtraFilter] Optional - Extra Filter
Value to filter by