Skip to content

Invoicing

Introduction

Invoicing Excelerator is a tool that allows users to enter and modify Sage 200 invoices and credit notes in Excel. It comes with a range of features that make the process easy and efficient. For example, you can

  • Invoices and credit notes can be created and amended individually or in bulk on a single sheet.
  • Existing invoices and credit notes can be downloaded and amended.
  • Some invoice and credit note 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 invoices and credit notes directly from Excel.
  • The usual Excelerator features are described in Introducing Excelerator.

To learn more about Invoicing Excelerator, check out the Quick Tutorial.

Info

To use this Excelerator to create and amend invoices, the user must have access to the following features (from the Invoicing section) in Sage:

  • Enter New Invoice
  • Amend Invoice
  • View Invoice

To use this Excelerator to create and amend credit notes, the user must have access to the following features (from the Invoicing section) in Sage:

  • Enter New Notes
  • Amend Credit Note
  • View Credit Note

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 the standard template S200InvoicingTemplate.xlsx which includes sheets which allow:

  • Single - Entry of a single order
  • Multiple - Entry of multiple orders

Options

Don't Clear Header Ranges?

This option lets you choose whether or not the single-cell ranges (usually found at the top of the spreadsheet) will be cleared when the Clear button is clicked. This is useful if you want to retain header information while entering single invoices for the same customer.

Delete existing lines when amending invoices?

This option controls Excelerator's behaviour when you save an existing invoice and there is a line number missing from the invoice you are saving, but present in the existing invoice in Sage. If this option is ticked, Excelerator will delete that line, but if it's not ticked", Excelerator will leave the line unchanged.

Ignore completed invoices?

This option determines which invoices are shown in the "Download" option. If it's ticked, completed invoices won't be shown, but if it's not ticked, all invoices will be displayed.

Ignore invoice line(s) with zero quantity?

This can be useful, for instance, if a sheet has a standard invoice set up with items already entered, and the user has to enter quantities. In these circumstances, zero is a valid quantity, which means that this item is not being returned.

Customer Order Form shows an example of this in use.

Ignore invoices having zero quantity in all lines?

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

Entering Invoices and Credit Notes

Info

Two ranges are included to help with amending existing invoices:

  • Line Number
  • Line Sequence Number

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

Entering Invoices

The screenshot below shows how to enter invoice items.

Standard Items

To add standard items to your order, enter the stock code in the "Stock or Additional Charges Code" field. You can browse the available codes by clicking the "Browse" button. After selecting the appropriate code, enter the desired quantity and price. Alternatively, the "Calculate Values" feature can automatically fill in the price range.

Additional Charges

If you need to add any extra charges like insurance or carriage to your purchase, simply enter the code for the extra charge in the "Stock or Additional Charges Code" section, along with the quantity required. To view a list of additional charges, right-click on the mouse while selecting a cell in the stock code section.

Free text item

Enter free text into the Item Description range with quantity and price for non-stock items.

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.

Amending Invoices

When making changes to an invoice, it is important to include the line number range, unless you are only modifying the header. The line number range is necessary to match the line on the Excel sheet to the corresponding line on the existing Sage invoice.

To modify an invoice, you can download it using the "Download " option. However, if a line number is missing from the worksheet when you save it, then Excelerator may delete that line after warning you, depending on the "Delete existing lines..." Options that you have set.

Entering and Amending Credit Notes

To create or modify a credit note, specify the document type range and include CreditNote in the range. You may browse for it. All other instructions from above apply.

Download Invoices

This feature works similarly to the invoice browse. You can choose not to include complete orders by selecting an option (see Options).

However, unlike the browse, you have the following choices:

- Clear before download: Any orders already entered on the sheet will be erased before downloading new ones.

- Append: The orders you're downloading will be added to the sheet after the previously entered orders.

- Append from current cell: The orders you're downloading will be added to the sheet starting from the current cell.

Calculate Values

To populate prices and values on your invoice, you can use the Calculate Values feature. However, please note that values already entered will not be overwritten. It is recommended to enter customer details and standard item quantities before clicking the Calculate Values button. You can see the Calculate Values option in action in the gif below.

Delete Invoice Item(s)

This feature allows you to delete invoice lines from Excelerator.

Info

This feature deletes lines on invoices already saved to Sage.

You cannot delete lines from invoices that have been printed. Also, you are not allowed to delete all the invoice lines - one line must be left.

When you select this feature, Excelerator will display all the items for the invoice number currently entered on the worksheet. If multiple invoices are on the worksheet, then the selected invoice will be shown.

Select the detail lines you want to delete. If you select "Add all", you will have to remove one line before you can continue. You will be asked to confirm the deletion, and then offered to download the invoice again.

Clear All

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

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

See also: Clear Excel Data

Invoicing Printing

To print SOP invoices and credit notes using Excelerator, you simply need to click on the Print button. However, please note that the invoices and credit notes must already exist in Sage and have the correct status in order to be printed. Additionally, you cannot print any new items on your worksheet until they have been saved to Sage.

When you choose the print option, you will be able to print all the SOP Invoice and credit notes document numbers on the sheet, or you can select specific ones to print. For more information on printing, check out Printing and The Sage Help on printing.