Creating New Templates

In addition to the standard templates, Excelerator allows easy and flexible creation of your own templates.

These can be empty templates for data entry or existing spreadsheets populated with data can be used.

Excelerator works by taking or putting data into designated areas on a spreadsheet. These areas are called ranges.

There is a fixed list or ranges for every Excelerator module.  

Some ranges are mandatory and must be created for Excelerator to work, others are optional.

Designing Templates

Open Excel and select for example, Sage 200 Excelerators > NL Journals > Designer.

The Designer panel will be displayed in the designated location as shown below.

Note, the red ranges are mandatory, the blue ranges are optional and the green ranges are for downloading data, only.

Header Ranges

These are for where there is only one item of data per post e.g Journal numbers and can only be a single cell.

Select the Excel cell where the first Header range is to start. In the example below, this is A2. 

In the Designer window, click on the range that needs to be added and click Add.  

Clicking on the added range will display a Confirmation (ToolTip) to describe the type of data that can be entered.

Note, the colour of the range name in the Designer window will change from red to grey, indicating that it is an added range.

The example shows, that the Journal Title (Header range) is added to the template.

Adding new row in the designer mode

Hovering the mouse over a range will display a tip to indicate the type of range i.e. Header, Detail or Both.

tool tips display

Detail Ranges

For multiple lines of data e.g Nominal Account Codes and Values, these should be columns of data of the same number of rows.

Detail ranges can be added in the same way as Header ranges but the number of rows must be specified.

Select the Excel cell where the first detail range is to start.  In the example below, this is A5.

In the Designer window, click on the range that needs to be added and click Add.

In the example, the Nominal Account has been selected and is assigned 50 rows.

Adding detail ranges

 

Header/Detail Ranges

Data that can be both Header or Detail.  For example, PL Invoices Excelerator allows a single sheet to be either one invoice, or multiple invoices.  Account Reference and Invoice Reference can be header for the former and detail for the latter. 

Browse

Click Add Browse to enable browsing on the Account Reference and other fields that can be browsed.

The button should rename to Remove Browse, if the browse has been enabled.

Click Exit to close the Designer Panel.

On the sheet, right-click cell A5 and select Browse Records.

Browsing records 

A window will open displaying all the Account Codes for the company that Excelerator is connected to.

Select the relevant code(s) and then click OK.

The selected codes will be copied to the included range(s) on the sheet.

Selecting and adding codes

Columns of information may be sorted in ascending or descending order.

It is possible to search for data using the Search box or by adding filters to the columns.

using the search box

Select OK to search for text or apply filters or Exit to exit the browse.