BoostExcel main content

Custom Field - Tutorial - Invoice Manager for Excel

Contents

This document was written for Excel Invoice Manager. If you are using Invoice Manager for Excel, please refer to Custom Fields for the updated documents and tutorials.

In this tutorial, we will look at how to create an invoice template for an automotive company, starting from the Standard Invoice.xls template. The resulting invoice template created in this tutorial is installed on your computer and located in the Automotive subfolder of the data file folder of Excel Invoice Manager.

The customized sample template is now published here on InvoicingTemplate.com at Automotive Sample Invoice Template.

Analyzing Requirements

First, let's look at the Automotive.xls invoice template and find out what is required to create this Automotive.xls template.

TemplateInvoice WorksheetPrinted Version
Standard Invoice Template (Invoice.xls)

Excel Invoice Manager default invoice template

The print result of Excel Invoice Manager default invoice template

Automotive.xls

Excel Invoice Manager Automotive template

The print result of Excel Invoice Manager automotive template

By comparing the two templates, we find out that the main differences are as follows:

  1. Invoice.xls has two taxes, whereas Automotive.xls has one tax.
  2. Automotive.xls does not include shipping information.
  3. Other cell names and controls need to be removed:
    • Sales Rep. Name (oknSalesRepName)
    • Ship Date (oknShipDate)
    • Ship Via (oknShipVia)
    • Terms (oknPaymentTerm)
    • Customer Country (oknWhoCountry)
    • Ship To-Selection button (image)
    • Sales Rep. Name-Selection button (image)
    • Ship Via-Selection button (image)
    • Terms-Selection button (image)
    • Customer Country (label)
    • Customer ID (label)
  4. Automotive.xls has 28 rows on its invoice body.
  5. We need the following custom fields:
    Product Table
    Field NameField TypeNote
    DiscDecimal (5,2) 
    CoreExtDecimal(14,4) 
    Invoice Header Table
    Field NameField TypeNote
    MiscDecimal (15,4)Entered manually on the Invoice worksheet.
    DealerExtTotalDecimal (15,4)The sum of all Dealer Ext. cells in the current invoice.
    DiscExtTotalDecimal (15,4)The sum of all Disc Ext. cells in the current invoice.
    CoreExtTotalDecimal (15,4)The sum of all Core Ext. cells in the current invoice.
    Invoice Body Table
    Field NameField TypeNote
    DiscDecimal (5,2)Corresponding to the Disc field in the Product table. That is, when saving an invoice, the Disc value retrieved from Product table will be stored in the Disc field in the Invoice Body table.
    CoreExtDecimal(14,4)Corresponding to the CoreExt field in the Product table.
    DealerExtDecimal(15,4)Equal to Price * Quantity. It is calculated automatically by Excel. Thus it is not necessary to create a corresponding field in the Product table.
    DiscExtDecimal(15,4)Equal to DealerExt - LineTotal.
    NetEachDecimal(15,4)Equal to Price * (1-Disc/100).
    NetExtDecimal(15,4)Equal to Quantity * NetEach.
    Note: In Automotive.xls template, the ProductID column is labeled as Part Number, the Price column is labeled as Dealer Each, and the LineTotal column is labeled as Net Ext.

Customizing Database

In this section, we will customize the database definition file and database file for our Automotive.xls example.

  1. In Windows Explorer, create a folder Automotive and copy FDINFO10.rst and Sample.mdb (or an empty database file created using Excel Invoice Manager) from the data file folder of Excel Invoice Manager into Automotive subfolder. Rename Sample.mdb to Automotive.mdb.
  2. In Windows Explorer, browse to the Start menu -> Programs -> Office-Kit.com -> Excel Invoice Manager -> Database Designer and click to launch it. You will see the Introduction screen of Database Designer.

    Customizing database step 1

  3. Click Next. This screen allows you to open a database definition file and save a backup.

    Customizing database step 2

  4. Click Open Database Definition File and browse to the Automotive folder we just created. Select and open FDINFO10.rst.

    Customizing database step 3

  5. Because the database definition file FDINFO10.rst was copied from the data file folder of Excel Invoice Manager, we can safely skip the database definition file backup process. Click the Next button to go to the following screen.

    Customizing database step 4

    The Tables list shows all tables that are modifiable. The Columns/Fields list shows all custom fields in the table currently selected, or shows all fields in the table currently selected if the Display Standard Fields box is checked. Standard fields are required by Excel Invoice Manager and are not modifiable.

  6. Now we create the first custom field Misc for the Invoice Header table, which is a decimal value with a precision of 15 and a scale of 4. Click Invoice Header in the Tables list, and then click the Add Field button. This displays the following screen.

    Customizing database step 5

  7. In Add a New Field screen, enter field name Misc. Select Decimal as its field type. Enter 15 and 4 in the Precision and Scale box respectively. Click the OK button to close the dialog box.

    Customizing database step 6

  8. You can see the field just added in the Columns/Fields list. In case you need to modify an existing custom field, just select the field in question and click the Modify Field button. To delete a field, select it and click the Delete Field button.

    Customizing database step 7

  9. Repeat the above steps to create all required custom fields.
  10. Click the Save button to save the modified database definition file.
  11. Click Next. Here we will apply the database definition file to databases.

    Customizing database step 8

  12. First, specify the database file by clicking the Select database file button and select Automotive.mdb from the Automotive folder.
  13. Because the Automotive.mdb database was copied from Sample.mdb, we will skip the Backup database step. (No backup is necessary in this example. However, if you try to modify your working databases and templates, it is strongly recommended that you backup all of them prior to making any changes.)
  14. Now click the Apply! button. Database Designer modifies the database structure according to the database definition file. When finished, it displays "OK" near the Apply! button, or displays error messages if there were problems found during the process.

    Customizing database step 9

  15. Click the Finish button to exit Database Designer.

Customizing Invoice Template

In this section, we will create the Automotive.xls template from the standard Invoice.xls template. Before starting, make sure you've read the Customizing invoice template chapter, which provides general information about customizing invoice templates.

  1. Copy the Invoice.xls template in the data file folder of Excel Invoice Manager into the Automotive subfolder. Name it Automotive.xls.
  2. Open Automotive.xls in Excel.
  3. Click the Settings button on the Invoice worksheet. Switch to Taxes tab. Choose one tax and set tax name to 'Sales Tax'.
  4. Unprotect the Invoice worksheet and switch it into design mode.
  5. Display row/column headers and grid lines.
  6. Remove all unused ActiveX controls. To remove an ActiveX control, just click it and hit the DELETE key.
  7. Remove all unused cell names.

    To remove a cell name in Excel 2000 - 2003, click Excel menu Insert -> Name -> Define. Browse to the name in question and select it. Click Delete.

    To remove a cell name in Excel 2007/2010, go to the Formula tab, in the Defined Names group, click Name Manager, select the name you want to delete, and then click Delete.

    Customizing Excel template step 1

  8. Move the following unused cell names out of the PRINT_AREA. These cell names cannot be deleted, as they are required for validating invoices. However, you can move them out of the PRINT_AREA or even put them into hidden rows/columns.
    • oknPayments
    • oknBalanceDue
  9. Move the Invoice Date (cell name oknInvoiceDate) and the Invoice # (cell name oknInvoiceID) to a free, unused area, such as column U and column V.

    Customizing Excel template step 2

  10. Right click the row header "7", and then insert a new row. Repeat 3 times to insert 3 rows.
  11. Move the company information area near the LOGO image to the area starting from the K6 cell. Enter company name, address, etc.
  12. Enter company name in the cells next to the LOGO image.
  13. Move the "Bill To" label and customer information cells to the proper place. Here we use 4 customer information cells:
    • oknWhoName
    • oknWhoAddress
    • oknWhoCityStateZip
    • oknWhoPhone
  14. Move the 4 ActiveX labels which correspond to the 4 customer information cells to their proper place or position.
  15. Right click the Name ActiveX label and click Properties. Set its TextAlign property to 1-fmTextAlignLeft. Repeat this step for other customer information ActiveX labels.
  16. Move the Customer-Selection icon button (magnifier icon) to the right of customer information labels.
  17. Move the View Customer Info button to its proper position. To make it the same width as the Save As New Customer button:
    1. Right click the Save As New Customer button and click Properties.
    2. Browse to the Width property and note its value.
    3. Click the View Customer Info button without closing the Properties box and enter the width value in its Width field.
    4. Close the Properties box.
  18. Select all Product ID cells by dragging your mouse. Move these cells outside the PRINT_AREA and place them in a free/unused space, such as column U.

    Customizing Excel template step 3

  19. Select all Quantity cells by dragging your mouse. In Excel 2000 - 2003 click the Merge and Center button on the Formatting toolbar once; Or in Excel 2007/2010 go to Home tab and click the Merge and Center button in the Alignment group. This will unmerge all Quantity cells.
  20. Select all oknQuantity_** cells by dragging your mouse. Move them to the first column on the invoice body. Change the first column header on the invoice body from Product ID to Qty.

    Customizing Excel template step 4

  21. Select all Product Description cells by dragging your mouse. Unmerge them. Move all cells named as oknProductName_** to a free area, such as column V. In the Aotomotive.xls template, we do not need the Product Description/Product Name column. However, this column is necessary when Excel Invoice Manager validates invoices. So the oknProductName_** cells cannot be deleted, but can be put into a hidden column.

    Tip: If the Product-Selection icon buttons are moved with the Product Description cells, just select all Product-Selection icon buttons by pressing CTRL key and clicking each of them, and then press the arrow key one or several times - this ensure the icon buttons and the product description cells are not overlapped.

  22. Move all oknProductID_** cells to column H. Fill the column header cell with text "Part Number".
  23. Unmerge the cell with text "Description" in it.
  24. Right click column I and select Insert. This inserts a new column next to the "Part Number" column.
  25. Enter "Disc %" as the column header. Name the following cells as oknDisc_1 to oknDisc_12.

    Customizing Excel template step 5

  26. In the next column (column J), enter "Dealer Each" as the invoice body's column header. Move all oknPrice_** cells to this column.
  27. In the next column (column K), enter "Dealer Ext." as the invoice body's column header. Name the following cells as oknDealerExt_1 to oknDealerExt_12 respectively.
  28. Repeat the above steps to setup these columns:
    Column NumberColumn Header Text on Invoice BodyCell Name
    LDisc Ext.From oknDiscExt_1 to oknDiscExt_12
    MNet EachFrom oknNetEach_1 to oknNetEach_12
    NNet Ext.From oknLineTotal_1 to oknLineTotal_12
    OCore Ext.From oknCoreExt_1 to oknCoreExt_12
  29. Create formulas to calculate cell values on the invoice body. Replace RowNumber in the following formulas with appropriate row numbers.
    Column NumberColumn HeaderFormula
    KDealer Ext.=ROUND(oknPrice_RowNumber*oknQuantity_RowNumber,2)
    LDisc Ext.=oknDealerExt_RowNumber - oknLineTotal_RowNumber
    MNet Each=ROUND(oknPrice_RowNumber * (1-oknDisc_RowNumber/100),2)
    NNet Ext.=ROUND(oknQuantity_RowNumber*oknNetEach_RowNumber,2)
  30. Select all rows on the invoice body by dragging your mouse on the row headers. Right click any of the row headers, click Row Height. Set Row Height to 12.75 to decrease the height of all rows on the invoice body.
  31. Move the following cells to its proper location. Create/format label cells as needed.
    Cell NameMove to This AddressLabel Text
    oknWhoIDK13Account #
    oknOrderIDL13P.O.
    oknDueDateM13Due Date
    oknInvoiceDateN13Date
    oknInvoiceIDO13Invoice #
  32. Clear all cells between the top row of the invoice body and the row we just filled in. If there are merged cells in this area, unmerge them.

    Customizing Excel template step 6

  33. Click and select column B and column F. Right click one of the headers, and then click Unhide on the menu.
  34. As you can see from the following screen shot, C13 ~ C18 are used to store default tax options. Select all of them and move them to a free area, such as C3 ~ C8.

    Customizing Excel template step 7

  35. Select all cells on the invoice body, including those in hidden columns, and then move them up to start on row 16.

    Customizing Excel template step 8

  36. Move the Quick View area several rows up to leave space for the Taxable checkboxes.
  37. Move all Taxable checkboxes up to the right place.
  38. Automotive.xls needs 28 rows on the invoice body, whereas the standard invoice template has only 12 rows. Now we need to add more rows to the invoice body. For a detailed tutorial about this, please refer to Multiple-page invoice templates.
  39. Create/set several cells for calculating totals of certain columns, as listed in the following table:
    Cell NameFormula
    oknDealerExtTotal=SUM(oknDealerExt_1:oknDealerExt_28)
    oknDiscExtTotal=SUM(oknDiscExt_1:oknDiscExt_28)
    oknCoreExtTotal=SUM(oknCoreExt_1:oknCoreExt_28)
  40. Fine-tune the invoice template, such as setting up fonts, colors, text labels, borders and number formats.
  41. When finished, in Excel 2000 - 2003 exit design mode by clicking the Design Mode button on the Control Toolbox toolbar; In Excel 2007/2010 exit design mode by clicking the Design Mode button in the Controls group of the Developer tab. Hide row/column headers and gridlines.
  42. Test the template.