BoostExcel main content

Summary

This tool adds or deletes the lines on the itemized detail section of the "Invoice", "Quote" and "Purchase Order" sheets, so that the sheets has the number of lines you specified.

This tool works for regular form only.

"Regular form" form means each column on the Excel sheet maps to a different database field, and each column has the same number of valid lines. For example, if a "Quote" sheet has two "Product ID" columns, it is not a regular form (because two columns on the sheet map to the same database field).

How to change the number of lines

  • Open your template as usual.
  • Go to the "Invoice" ribbon tab.
  • Click the "Tools" button, and then the "Set the Number of Lines on the Detail Section" button. This shows the dialog window below.

    How to set the number of lines in Invoice Manager

  • Fill in the number of lines you want on each sheet.

    If an entry is 0 (zero), Invoice Manager will try to detect the max line number on the fly when you clicking "Clear & New" and "Save to DB".

    If a sheet, such as "Quote", is missing from your template, the corresponding text box is disabled.

  • Click the "Apply" button.

Fine tune the modified sheets

The template is in design mode once the automated modifications are done. You may need to do one or more steps of the following to make the form look elegant.

Add / remove borders, set background colors

  • Right-click the cell you want to modify.
  • Choose "Format cells" from the shortcut menu.
  • To set the background color, go to the "Patterns" tab; to add / remove borders, go to the "Borders" tab.

Delete form button and checkboxes

If you add new lines, Invoice Manager adds "Taxable" checkboxes and links them to correct "oknTaxable_linenumber" cells automatically if the existing checkboxes are Excel form controls.

If your template is very old (before 2014) and the checkboxes on your sheet are ActiveX controls, it will not replicate those controls. In this case, you can send us your template for an update.

If you delete lines from the sheet, Invoice Manager deletes the corresponding "Taxable" and "select a product / item" buttons too. However, if the taxable boxes are not correctly named, the deletion failed. In this case, you need to delete the unwanted controls manually.

To delete a control from the sheet, right-click the border of the control, and then choose "Delete" from the shortcut menu.

Make sure the template is printable

Click Excel menu File / Print to preview the print result of the sheet. This also shows how the PDF document will look like. Make sure the form is printable on the number of pages you desired. Make sure you preview each of the sheet that you use – invoice, quote and purchase order.

If you add a few new lines and the sheet is extent to more pages than you want, there are several ways to reduce the number of pages it requires.

  • Modify top, bottom, page header and page bottom margins. Do this using the Page setup dialog box. See more samples on this topic here.
  • Modify line heights.

    To do this, drag mouse on the row headings to select all lines, right-click one of the selected row headings, choose "Line height". You can also do this for the lines on top or bottom of your sheet.

If you delete a few lines from the sheet, you can also modify the line heights to make the form looks elegant.

Test the template

Invoice Manager modifies the key formulas to reflect the new number of lines. These includes the line total cells, subtotal, and taxable subtotal. If your template is a customized version, or if you added custom fields and applied new formulas, a thorough and comprehensive test is required before production usage.

To do this test, first exit design mode, and then fill in all the fillable data cells. Carefully review the result of each calculable cells.

It is also necessary to save and reload invoices, quotes and purchase orders to make sure the form works correctly.

  • Fill in all the data on sheet.
  • Click "Save To DB" to save it to the database.
  • Click "Invoices" (or "Quotes", "Purchase Orders") on the "Invoice" ribbon tab, click and select the entry you saved, and then click "Edit In Excel".
  • Make sure the result meets what you entered on the sheet.