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

The spill functions can retrieve any Rows or Columns of data, as discussed at the top of the page.   We begin by getting a list of useful codes for your sheet, and then move on to obtaining values from the Chart of Accounts, and finally get Debtors List.

2.1 Code Spill - Get Heading and GL List

The CXL.CodesSpill( CompCodeRange, "GLCodesByHead") will return a list of GL Account codes CONSOLIDATED from one or more companies given in the CompCodeRange. The parameter CompCodeRange being one or more cells e.g. C2:C6 or just C2.

By adding an optional parameter "Y2", to give CXL.CodesSpill( CompCodeRange, "GLCodesByHead", "Y2" ) we can ask Consolidated XL to filter out any accounts which have had no value for over 2 years.

   Reference Guide CXL.CodesSpill( CompCodeRange, GLHeadingCode, [Options], [Query] )

This is used to drive the CXL Wizards. The GL-based Wizards create a sheet called "CXL Lookups" and populate it with the functions to get the consolidated list of GL Account codes and headings. They then allow the sheet to be evaluated. They then build the P&L or Balances sheet by working through this list based on the headings code in column R (in the example below). They give a list of GL Account Codes to list in the report under each heading.

   C5 = blank
   Y5 = "Get Code:"
   Z5 = "GLCodesByHead" + Name cell "CXLGetCode"
   Z7 enter or copy and paste =CXL.CodesSpill( CXLComp, CXLGetCode )

You will get a list of GL Accounts sorted by GL Heading:

   Change Z5 to "GLHead" to get a list of all headings, sections and calculated totals.

   Change Z5 to or "CC" (Sage), "TRACKING1" (Xero), "CLASS" (QuickBooks) or "DEP" (Sage), "TRACKING2" (Xero), "LOCATION" (QuickBooks) to get a list of all cost centres.

Note: The CXLGetCode and the Options parameter, we have seen previously, ignores any text after the first word so can have a description after the first word.

   Let's put Z5 back to "GLHead" so we can reference the GL codes later

2.2 Spill GL Values using one function

More Magic... Now Let’s use the =CXL.GLValSpill() to get a whole bunch of GLs and Periods into a row.

   Reference Guide CXL.GLValSpill( CompCode, GLCode, [NoYears], [NoPeriods], [CostCentre], [Department], [Options], [Style], [FromYear], [FromPeriod] )

First, we will start by adding some more parameters:

   Delete row 14 to 20 to remove previous example
   C5 = blank
   B15 = "Years:'
   C15 = "2" + Name cell "CXLYears"
   B16 = "Periods:"
   C16 = "6" + Name cell "CXLPeriods"
   B17 = "Style:"
   C17 = blank + Name cell "CXLStyle"
   B20 = "200"
   In D20 enter =CXL.GLValSpill( CXLComp,B20,CXLYears,CXLPeriods,"",CXLRep,CXLOptions,CXLStyle )

Now we should see the sales Turnover, for GL 200, for 2 years and 6 periods:

   In C8 enter "Tim" and the functions will then Filter by Rep "Tim" in Xero Tracking Code 2 ( AKA Classes and Locations in QuickBooks and Cost Centres and Departments in Sage Accounting) to give:

   Now add the other GL's 230 and 260, and then "#REVENUE" (GL Heading for Sales) and copy formula down.

2.3 Add Headings to Spill

But of course, we are missing the headings.:

   In B19 enter "^" and drag the formula from D20 to D19, and we should have headings. Bold row 19 and 23 to give

The "#REVENUE" code will get the total for all GL's under the REVENUE heading. See Xero, QuickBooks or Sage for these heading lists. We can even get the Net Profit by using #CalcNetProfit.

If you put Z5 back to "GLHead", then you have a list of all these codes in Z7 onwards.

   Try altering the Year (C14) and Period (C15) cells, and see the affect.
   Now change "Age" options to "W", "AQ" and the Rep "Sam" etc.

   And finally C6 to "AQ,ShowYTD" to change the Year totals to YTD figures.

   And finally C6 to "AQ,ShowBoth" to change the Year totals to YTD figures.

Great. This function is easier and faster to get data than the granular functions we used earlier. Granular functions are better for consolidation of multiple companies.

But this function can also get a whole section…

   Clear any Age (C5) and Rep (C8) criteria, and copy the complete "#REVENUE" row 23 to row 26

Copy REVENUE row from 23 to 26

2.4 Spill GL Values by GL Headings code

   Now edit B26, with a full stop on the end, to "#REVENUE." We now have all child GL's of REVENUE heading.

Add GL Accounts under GL Heading using "."

   Next add '^' to add headings.

Add Column Headings with "^"

   And then add '+'' to add total at bottom.

Add “+” to add totals

   And then add '>' to remove any rows with zero value to give "#REVENUE.^+>".

Add “>” to filter out zero value rows

   In C8 enter "sam", and now we see it filtered for Rep "Sam".

Sales filtered by Rep "Sam" for 2 years and 6 Accounting Periods

   You can try other values, such as "No Analysis", "Tim", "Jane" and "Peter"
   Blank C8 before moving on

2.5 Get P&L from one function

   Now change B21 to "#ReportPL" and will get the whole P&L for a given number of periods.
   We can add ">" to give "#ReportPL>" to filter zero rows.

Get P&L from one function for 2 years and 6 Accounting Periods

2.6 Change Column Headings Format

   C16 change to "mm/yy" which will change the heading style (Click here for example).

Changing the heading style

   C16 change to "mm y".

Changing the heading style

2.7 Get P&L from one function filtered by Cost Centre

   In C5 change to "AQ".
   C8 back to Rep "Sam".

Get P&L for Rep "Sam" in the Last 6 Accounting Quarters, with This and Last Year totals

2.8 Get All Current Bank Balance in one function

   Set B100 to "#BANK.^+" for Current Liabilities (note no zero row filter ">").
   Then in D100 =CXL.GLValSpill( CXLComp,B100 )

You should now have:

All Current Bank Balances

2.9 Get All Current Liabilities Balances in one function

   Set B110 to "#CURRLIAB.^+>" for Currenct Liabilities (note with zero row filter ">").
   Then in D110 =CXL.GLValSpill( CXLComp,B110 )

You should now have:

Current Liabilities Balances which are not zero

2.10 Get All Current Assets Balances in one function

This time we are going to move the special control codes to the Options parameter."

   Set B120 to " #CURRENTASSETS " for Current Assets.
   Then in D120 =CXL.GLValSpill( CXLComp, B120, 0, 0, "", "", ".^+>" )

You should now have:

Current Asset Balances which are not zero

2.11 Get Balance Sheet from one function

   Set B130 to "#ReportBal" for the whole balance sheet.
   Then in D130 =CXL.GLValSpill( CXLComp, B130, 0, 0, "", "", ".^+>" ) or copy D120 to D130

Get Balance Sheet from one function

Note: =CXL.GLValSpill( CXLComp, B130, 0, 0, "", "", ".^+>" ) is the same as =CXL.GLValSpill( CXLComp, "#ReportBal.^+>" )

Next Steps

   Try out the Income Statement/P&L Wizard or the Get GL Values Demo Wizard which creates several sheets showing the different ways of getting GL values from your favourite online accounting system.

   See the "Drill Down" page for an explanation on the drill down from these figures PRO