Customer Delivery Addresses
Introduction
Please see our Quick Tutorial on using Customer Delivery Address Excelerator.
The Customer Delivery Addresses Excelerator allows Sage 200 customer delivery addresses to be maintained from Excel.
Features include:
- Delivery addresses can be created and amended from Excel.
- A customer's existing delivery addresses can be downloaded, for one customer or for many.
- The default delivery address can be set from the sheet.
- The VAT details held against each address (VAT number, VAT code and VAT country code) can be maintained.
- Addresses that still exist in Sage but are no longer on the sheet can be deleted as part of the save.
- Addresses can be maintained in more than one Sage company from a single sheet.
- Browse Sage data and pick from browse to enter on the sheet.
- Flexible design for the spreadsheet templates.
- The usual Excelerator features, as described in Introducing Excelerator.
The typical workflow will be either to enter new delivery addresses for a customer and then save them, or to download a customer's existing addresses and then amend those addresses.
Sage Permissions
Info
To download customer delivery addresses, the user must have access to the following feature in Sage:
- Customer Delivery Addresses (Sales Order Processing > SOP Maintenance)
If this feature is not available to your Sage user, Excelerator reports that the download is disabled in Sage and no data is fetched. Please contact your Sage administrator.
See also: Sage 200 Configuration
Standard Templates
Codis provides the template S200CustomerDeliveryAddress.xlsx. You can, of course, amend this template or create your own. (See Designing Templates ).
The S200CustomerDeliveryAddress.xlsx includes a single sheet which allows:
- Sheet1 - Entry of one customer, with that customer's delivery addresses entered as rows.
The Account Reference is the module's control range. You can use it either as a single cell, in which case every address on the sheet belongs to that one customer, or as a column, which allows the delivery addresses of several customers to be maintained on one sheet.
The Ranges
The ranges are grouped under three headings: Customer, Delivery Addresses Details and VAT Details. All of the ranges can be used as either single cells or columns, so the layout is entirely up to you (see Designing Templates).
Customer
| Range | Notes |
|---|---|
| Account Reference | Required. Must be an existing Sage customer account. This is the control range and can be used as a header. |
| Account Name | Filled in when you download. It is not used to update the customer record. |
Delivery Addresses Details
| Range | Notes |
|---|---|
| Index | Identifies an existing delivery address when amending. See Entering and Amending Delivery Addresses. |
| Description | Required. Sage's name for the address. Must be unique for the customer. |
| Postal Name | |
| Address Line 1 to Address Line 4 | |
| City | |
| County | |
| Contact | |
| Delivery Postcode | |
| Country Code | The delivery address country code, for example GB. Must be a country code set up in Sage. |
| Telephone | |
| Fax | |
| Set as Default | Enter Y to make this the customer's default delivery address, otherwise N. Only one address per customer may be set to Y. |
| Update Status | Shows the status of each address - new, update, saved or invalid. |
| Error Found | Shows the first error found for the address when validation fails. |
| Sage Company | Lets you maintain addresses in more than one Sage company from one sheet. See Multiple Companies. |
VAT Details
| Range | Notes |
|---|---|
| VAT Number | |
| VAT Code | The VAT code's description, as shown in Sage. Must be an existing Sage tax code. |
| VAT Country Code | Must be a country code set up in Sage. |
For a new address, all three VAT values default from the customer account (its VAT registration number, default tax code and country code), so you only need to enter them when the address differs from the customer.
Options
Don't Clear Header Ranges?
This option controls whether single-cell ranges (those generally at the head of the spreadsheet) will be cleared when the Clear All button is clicked.
Browses
Like other Excelerators, Customer Delivery Addresses Excelerator allows you to browse and download Sage data.
Browses are available on:
- Account Reference - Sage customers.
- Country Code - Sage country codes.
- VAT Code - Sage tax codes.
- VAT Country Code - Sage country codes.
- Set as Default - Yes/No.
Download
The download fetches all of the delivery addresses that Sage holds for the customers already entered on your sheet.
- Enter one or more account references in the Account Reference range.
- Click Download on the ribbon.
Excelerator clears the sheet's ranges first, asking you to confirm, and then writes out every address it finds for each account reference, starting from the first row. The Index range is filled in as part of the download, so the addresses that come back are ready to be amended and saved.
If no account reference has been entered, you are told that no customer was found and nothing is downloaded.
Entering and Amending Delivery Addresses
Enter one row per delivery address. A Description is required for every address - it is the name Sage shows for the address - and two addresses for the same customer cannot share a description.
Whether an address is created or amended is decided by the Index range:
- An index that matches an address already held for the customer amends that address.
- A row with no index, or an index that does not match, creates a new address.
Indexes are the positions of the addresses as Sage holds them, numbered from 1, and they are filled in for you by the download. In practice this means you should download before amending, rather than typing indexes by hand.
Blank cells are ignored, so an existing value in Sage is left as it is when the corresponding cell on the sheet is empty. You only need to fill in the ranges you want to change. See also Treatment of Blank Cells in Excelerator Ranges.
Setting the Default Address
Enter Y in Set as Default against the address that should be the customer's default delivery address. Only one address per customer may be marked Y; if more than one is, validation fails.
Marking an address Y makes it the default. Entering N does not, on its own, remove the default from an address that is already the default in Sage - to move the default, mark the address that should now be the default with Y.
Addresses Missing from the Sheet
If the customer has addresses in Sage that are not on the sheet, Excelerator warns you:
Existing customer 'account' in Sage contains lines which are not present in items.
You then choose either to delete those addresses from the customer or to leave them alone. Where the customer has more than one address on the sheet, the choice can be applied to all of the remaining warnings.
Warning
If you do not include the Index range on your sheet, every address the customer already has in Sage is treated as missing. Choosing to delete at that point removes all of the customer's existing addresses and replaces them with the addresses held on the sheet.
Validate
Validate will check that the data on the spreadsheet is compatible with Sage accounting rules. It validates all the data and displays any errors in a new panel. Sage is not updated.
See also: Validate Sage Data.
The checks made by this module include:
- The account reference must be an existing Sage customer.
- A description must be entered for every delivery address.
- Two addresses for the same customer cannot have the same description.
- Only one address per customer can be set as the default.
- The VAT code must be an existing Sage tax code.
- The Country Code and VAT Country Code must be country codes set up in Sage.
Save to Sage
Click Save to Sage to write the addresses to Sage. The whole sheet is validated first and, if anything fails validation, nothing is saved. This allows the sheet to be corrected and the save attempted again without part of the data having already been written.
See also: Save to Sage.
Clear All
This menu option will clear down the data in the Excelerator ranges.
See also: Clear Excel Data.
Multiple Companies
Delivery addresses for several Sage companies can be maintained on one sheet by including the Sage Company range against each address.
Info
The Sage Company range cannot be used at both address level and as a single header cell on the same sheet, and when it is used at address level the Update Status range must also be on the sheet. This is because Excelerator saves to each company in its own transaction, so it is essential to track what has been saved.
See Using the Item-Level Company Range and Updating Multiple Companies from One Sheet.