Get Codes Spill Function

Consolidated XL provides the following powerful spill function to get a list of GL accounts, by GL Major Headings (Click for Xero, QuickBooks or Sage list) or Cost Center/Department, derived from one or consolidated from many connected companies. Consolidated XL Wizards utilise those to quickly produce a Profit and Loss, Balances sheet, Dashboard or Sales Report, etc.

   See Wizards and Examples for more information. See the "CXL Lookups" sheet they create.

Extract Codes Function

CXL.CodesSpill() will return a list of GL Account codes contained in all companies, defined in the CompCodeRange parameter, for reporting. This function is used in the Trial Balance, P&L/Income statement, and Balancesheet Wizards to create a Consolidated list of GL Accounts for all Companies selected.

If the CompCodeRange is a range, then Consolidated XL will return a list of account codes contained in all companies for reporting. This function is used in the Trial Balance, P&L/Income statement, and Balancesheet Wizards to create a Consolidated list of GL Accounts for all Companies selected.

You will get Excel error "#SPILL!" if the function has not got enough space to write to adjacent cells.

The wizards use CXL.CodesSpill(CompCodeRange, "GLCodesByHead","Y2") to write the GL Accounts to the Balance Sheet or Income statement
It will return a list of GL Accounts from multiple companies consolidated into one list, removing any not used for 2 years "Y2" in options.


Above we can see the same function used to get Cost Centres and Departments in Sage Accounting,
AKA Tracking Codes in Xero, Classes/Locations in QuickBooks.

   For a tutorial on this function, click here

Parameters:

Parameter	Description
CompCodeRange	Short code assigned by Consolidated XL, or by you to identify your company Or a Range of all Company codes for multiple companies to Consolidation.
CXLGetCode	Consolidated from many companies Values from Major Headings. “#SALES”,”#COS”,”#PROFIT”,”#OVERHEAD” See Xero Major Headings, QuickBooks Major Headings, and Sage Major Headings for a more detailed list "CC" - for list of Cost Centres (Sage), Tracking Code1 in Xero or Class in QuickBooks * "DEP" - for list of Department (Sage), Tracking Code2 in Xero or Location in QuickBooks * "TRACKING1" "TRACKING2" - Tracking Codes in Xero "CLASS" - Class in QuickBooks "LOCATION" - Location in QuickBooks "CCN", "DEPN", "TRACKING1N", "TRACKING2N", "CLASSN", "LOCATIONN" but does not included "None" "GLSelectCodes" - List of all GL Codes for selections "GLCodesByHead" - List of all GL Codes in Heading order * "GLHead" - List of all GL Heading Codes only * "ContactGroup" - List of Contact Groups (Xero) "ContactGroupN" - as "ContactGroup" but does not included "None" "StockGroup" - List of Stock Groups "StockGroupN" - as "StockGroup" but does not included "None" None Consolidated "ItemSelectCodes" - List of all Stock Codes (1000 Max) "ContactSelectCodes" - List of all Contact Codes (1000 Max) * = Wizards create a sheet called "CXL Lookups" listing thses
[Options]	Optional For GL Lists, if contains "CODE" will just list Codes "INDENT" will indent sub Codes (QuickBooks) "INCHEADINGS" will indent sub Headings (QuickBooks) "Y1"..."Y5" will filter GL Accounts Not Acitve for 1...5 year
[Query]	Optional Future Use