Skip to content

Sales Orders

Introduction

Sales Order Excelerator is a tool that enables users to create and modify Sage 200 orders using Excel. It comes with several useful features, including:

  • The ability to create and modify sales orders in bulk or individually on a single sheet.
  • Existing Sales Orders can be downloaded and amended.
  • Existing Quotations can be downloaded and saved as sales orders.
  • Some Sales Order header details can be amended without their details.
  • Browse Sage data and pick from browse to enter on the sheet.
  • Ability to print existing Sage orders directly from Excel.

For more information about other Excelerator features, please check out the "Introducing Excelerator" guide and see this Quick Tutorial on using Sales Order Excelerator.

Info

To use Sales Orders Excelerator, the user must have access to the following features in Sage:

  • Enter New Order - Full / New Sales Order (Web Portal)
  • Amend Sales Order
  • Sales Orders List

See also: Sage 200 Configuration

Standard Templates

Templates are working examples of Excel worksheets for use with Excelerator. You can use the standard templates as they are, amend them using the Template Designer, or create your own templates from scratch.

Codis provides a single template workbook S200SOPEntryTemplate.xlsx.

The S200SOPEntryTemplate.xlsx includes sheets which allow:

  • Single - Entry of a single order
  • Multiple - Entry of multiple orders
  • HeaderOnly - Amendment of order headers.

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 button is clicked.

This can be useful if you want to keep header information when, for instance, entering a series of single orders for the same customer.

Delete existing lines when amending order?

This option controls the behaviour of Excelerator when saving an existing order and when there is a line number missing from the quotation you are saving that is present on the existing order in Sage.

If this option is "Yes", Excelerator will delete that line. If this option is "No", Excelerator will leave this line unchanged.

Ignore Completed Orders?

This option affects which orders are shown in the Download Sales Orders option.

If it is ticked, the completed orders will not be shown. Otherwise, all orders will be shown.

Ignore quotation line(s) with zero quantity?

If this option is not ticked, then any order lines where no or zero quantity is entered are considered as having incomplete data by Excelerator, resulting in an error when validating or saving.

If ticked, then these lines will be ignored. This can be useful, for instance, if a sheet has a standard order set up with items already entered, and the user must enter quantities. In these circumstances, zero is a valid quantity, meaning this item is not being ordered.

Customer Order Form shows an example of this in use.

Ignore orders having zero quantity in all lines?

Used with the "Ignore order line(s) with zero quantity?" option. If all detail lines for an order have zero quantity, then this order will not be created, but the data is considered valid, and other orders on the sheet will be processed.

Validate Duplicate/Existing Customer order number

If this option is ticked, then Excelerator will check that the entered customer order number does not already exist for the entered customer. If it does, a warning will be displayed, but you will have the option to continue to save.

Entering and Amending Orders

Info

Two ranges are included to help with amending existing orders:

  • Line Number
  • Line Sequence Number

Neither of these ranges needs to be used if you are entering new orders.

Entering Orders

The screenshot below shows how to enter sales orders items. But the same principles apply for sales quotations, sales returns, purchase orders and purchase returns.

Standard Items

To enter standard order items (stock items, miscellaneous items, labour and services items), enter the stock code in the "Stock or Additional Charges Code" range. You can browse to pick a code or codes (see Browses).

Enter the quantity, and the price (or use Calculate Values to populate ranges such as price).

Additional Charges

To enter Additional Charges (such as insurance and carriage), enter the additional charges code into the "Stock or Additional Charges Code" range along with a quantity.

You can browse on additional charges by right button clicking when a cell in the stock code range is selected.

Free text item

These are other non-stock items. Enter the free text into the Item Description range, with a quantity and price.

Comment Lines

Enter the comment test into the Item Description range.

Info

When entering Standard Items or Additional Charges, you can use Calculate Values to get the price.
For Free Text items, you must enter a price.

Info

You can enter multiple lines using Alt-Enter in the Excel cell entry when entering item descriptions.

Calculate Values

The Calculate Values option will populate prices and values on your sales orders. Values already entered will not be overwritten.

As prices can depend on the customer and standard item quantities being ordered, these should be entered before clicking the _Calculate Value_s button.

Browses

Like other Excelerators, Sales Order Excelerator allows you to browse and download sage data.

You can browse on Sage products, customers, tax codes, delivery addresses and other data. After selecting a customer from the customer browse, Excelerator will populate the name and address fields. After selecting a product from the product browse, Excelerator will populate product-related data but not the price. To populate the price, use the Calculate Values option.

Changing the Delivery Address

When you select a customer using the browse or fast entry, the order's delivery address will be populated with the default address.

But you can pick from other delivery addresses for the customer...

Download Sales Orders

This option functions in a similar way to the sales orders browse. It will display a browse, and the selected order or orders will be downloaded onto the worksheet.

An option can be used to choose not to include complete orders. (see Options).

Unlike the browse, you have the following options:

Clear before download

Any orders already entered on the sheet will be cleared.

Append

The orders you are downloading will be added to the sheet after any orders already entered.

Append from current cell

The orders you are downloading will be added to the sheet from the current cell

Download Sales Quotations

This option will list quotations. These can then be downloaded and converted to a sales order.

Delete Order Items

Excelerator offers two ways to delete order lines.

Context Menu Delete Item(s) button

To delete items, select the item(s) by clicking in the line number range (or any detail range) and clicking on the right button on your mouse, then selecting "Delete Item(s)".

Info

You should only use the context delete button after downloading or saving to Sage.
The order on the worksheet must appear as it does in Sage.

This button will only appear on the context menu when the line number range is present, and a line number is entered.

This feature works best when amending a single order.

Info

If amending a single order, you can select multiple lines to delete.

Ribbon "Delete Order Items" button

This menu option will allow you to delete lines from the sales order.

You should download a single order. Click on the delete option, and you will given a list of lines to choose which to delete.

Printing

You can print sales orders from Excelerator by clicking the Print button.

  • The Sales Orders must already exist in Sage. You cannot print new sales orders on your worksheet until you have successfully saved them to Sage.
  • The print option will print all the sales order document numbers on the sheet or the ones you select.

See Printing

Use Cases

Customer Order Form

Excel customer forms are sent to customers to complete in this use case. Each Excel form will have a number for a single Sage customer on it.

Expected order lines are already completed on the form, and these areas are locked in Excel (although no password will be applied in any sample we provide).

This format also utilises the "Ignore order lines with zero quantity" option. Ticking this option allows the sheet to be saved immediately when the customer only fills in quantities for the order lines they want.

Clear All

This menu option will clear down the data in the Excelerator ranges.

You can choose to just clear down the detail ranges by changing the "Don't Clear Header Ranges" under Options.

See also: Clear Excel Data.

3rd Party Addin Support

Eureka MMS178 Excelerator support

https://eurekaaddons.co.uk/wp-content/uploads/2015/02/Plus-Pack-Data-Sheet-MMS178-Stock-Code-Aliasing.pdf