The Smart Workpapers Add-In has built-in 'Get' and 'Set' functionality, which allows a worksheet to Get data from the connected Xero file and Set that data into a named range. Every worksheet which is inserted into a file is checked for these named ranges and information is sourced from the connected file at the time of import. This article outlines the named ranges which select data from Xero.
Note: The information will not change regardless of a connection reset. It only occurs once when the worksheet is imported.
Trade debtors named ranges
Each of these named ranges are require to be attributed to a single column of data.
Note: Each named range needs to start and end on the same row within the worksheet
Model_Debtors.Balances.Detail
Model_Debtors.Balances.Balance
Model_Debtors.Balances.GST
Model_Debtors.Balances.Reference
Model_Debtors.Balances.Band30Days
Model_Debtors.Balances.Band60Days
Model_Debtors.Balances.Band90Days
Model_Debtors.Balances.Band90DaysPlus
Trade creditors named ranges
Each of these named ranges are require to be attributed to a single column of data.
Note: Each named range needs to start and end on the same row within the worksheet
Model_Creditors.Balances.Detail
Model_Creditors.Balances.Balance
Model_Creditors.Balances.GST
Model_Creditors.Balances.Reference
Model_Creditors.Balances.Band30Days
Model_Creditors.Balances.Band60Days
Model_Creditors.Balances.Band90Days
Model_Creditors.Balances.Band90DaysPlus
Wages and superannuation named ranges
Each of these named ranges are to be attributed to a single cell within a worksheet.
Month | Gross wages named ranges | PAYGW named ranges | Super named ranges |
July | Model1_Wages.Gross | Model1_Wages.Tax | Model1_Wages.Super |
August | Model2_Wages.Gross | Model2_Wages.Tax | Model2_Wages.Super |
September | Model3_Wages.Gross | Model3_Wages.Tax | Model3_Wages.Super |
October | Model4_Wages.Gross | Model4_Wages.Tax | Model4_Wages.Super |
November | Model5_Wages.Gross | Model5_Wages.Tax | Model5_Wages.Super |
December | Model6_Wages.Gross | Model6_Wages.Tax | Model6_Wages.Super |
January | Model7_Wages.Gross | Model7_Wages.Tax | Model7_Wages.Super |
February | Model8_Wages.Gross | Model8_Wages.Tax | Model8_Wages.Super |
March | Model9_Wages.Gross | Model9_Wages.Tax | Model9_Wages.Super |
April | Model10_Wages.Gross | Model10_Wages.Tax | Model10_Wages.Super |
May | Model11_Wages.Gross | Model11_Wages.Tax | Model11_Wages.Super |
June | Model12_Wages.Gross | Model12_Wages.Tax | Model12_Wages.Super |
GST named ranges
Each of these named ranges are to be attributed to a single cell within a worksheet.
The examples below are for the month of July - being month 1. Change the named range for each of the months in the financial year. Example:
October: Month 4
Total Sales: Model4_BASLabelG1
BAS label | Named ranges |
Sales | |
Total Sales | Model1_BAS.LabelG1 |
Export Sales | Model1_BAS.LabelG2 |
Other GST Free Sales | Model1_BAS.LabelG3 |
Input Taxed Sales | Model1_BAS.LabelG4 |
GST On Sales | Model1_BAS.Label1A |
Purchases | |
Capital Purchases | Model1_BAS.LabelG10 |
Non-Capital Purchases | Model1_BAS.LabelG11 |
GST on Purchases | Model1_BAS.Label1B |
Wine Equilisation Tax (WET) | |
Wine Equilisation Tax | Model1_BAS.Label1C |
Wine Equilisation Tax Refundable | Model1_BAS.Label1D |
Luxury Car Tax (LCT) | |
Luxury Car Tax | Model1_BAS.Label1E |
Luxury Car Tax Refundable | Model1_BAS.Label1F |
Wages | |
Gross Wages | Model1_BAS.LabelW1 |
Amounts Withheld from W1 | Model1_BAS.LabelW2 |
Other Amounts Withheld | Model1_BAS.LabelW3 |
Amounts withheld were no ABN is Quoted | Model1_BAS.LabelW4 |
Total Amounts Withheld (Total of Label W2+W3+W4) | Model1_BAS.LabelW5 |
PAYG Withheld (Copy of Label W5) | Model1_BAS.Label4 |
PAYG Instalment (PAYGI) | |
PAYG Instalment Income | Model1_BAS.LabelT1 |
PAYG Instalment (Calculated) | Model1_BAS.Label5A |
Credit from PAYG Inst. Variation | Model1_BAS.Label5B |
FBT Instalment | |
FBT Instalment | Model1_BAS.Label6A |
Credit from FBT Inst. Variation | Model1_BAS.Label6B |
Deferred Amounts | |
Deferred Company/Fund Instalment | Model1_BAS.Label7 |
Deferred GST | Model1_BAS.Label7A |
Fuel Tax Credits (FTC) | |
Fuel Tax Credit | Model1_BAS.Label7C |
Fuel Tax Credit Overclaim | Model1_BAS.Label7D |
Rental property named ranges
Each of these named ranges are to be attributed to a single cell within a worksheet.
Rental Property Name and Detail
Model_Rental.PropertyName
Model_Rental.Address
Model_Rental.DateFirstRented
Period Specific Details
Model_Rental.NumberOfWeeksRented
Model_Rental.Distribution
Income Details
Model_Rental.GrossRent
Model_Rental.OtherIncome
Expense Details
Model_Rental.Advertising
Model_Rental.BankCharges
Model_Rental.BodyCorporate
Model_Rental.BorrowingExpenses
Model_Rental.Cleaning
Model_Rental.Rates
Model_Rental.Depreciation
Model_Rental.Gardening
Model_Rental.Insurance
Model_Rental.Interest
Model_Rental.LandTax
Model_Rental.Legal
Model_Rental.PestControl
Model_Rental.PropertyAgent
Model_Rental.Repairs
Model_Rental.CapitalWorks
Model_Rental.Travel
Model_Rental.WaterCharges
Model_Rental.Sundry
Total Details
Model_Rental.TotalExpenses
Model_Rental.NetIncome