Moving cells and adjusting the layout of invoice form
The invoices for insurance agency don't require the "Ship To" fields, and many other fields in the default invoice template should be moved to better meet the business requirements of an insurance agency.
The most powerful and flexible feature provided by Microsoft Excel, and supported by Excel Invoice Manager, is that you can move cells/controls to quickly get a new invoice form based on an existing invoice form you already have. Moving a cell means to assign a new cell (the target cell) all the properties of the source cell, including fonts, formats, formulas, names, borders, lock/unlock properties, etc., and once the moving operation is done, clear all the properties on the source cell.
To move a cell or control in the invoice template, follow the steps below.
- By default the invoice template is protected. This prevents end-users from breaking the invoice form accidently. To remove the protection before modifying the template, in Excel 2000~2003 click menu command Tools -> Protection -> Unprotect Sheet; or if you are running Excel 2007/2010, click Review tab -> Changes group -> Unprotect Sheet. For detailed instructions, see Customizing Invoice Template - Protection.
- Switch to design mode. For detailed instructions, see Customizing Invoice Template - Design Mode.
- Click the cell or control to select it; or if you want to move a group of cells, drag your mouse to select all the cells; or if the cells/controls to move are not adjacent, push the CTRL key on your keyboard and click the cells/controls one by one.
- Now move the selected cell / cells / control to the new location.
- Exit design mode.
- Protect the Invoice worksheet once you've finished.
- Save your invoice template by clicking Excel menu File -> Save in Excel 2000/XP/2003, or by clicking Office button -> Save in Excel 2007, or by clicking File -> Save in Excel 2010.
Custom fields used by the car insurance agency invoice template
The invoice template requires an additional column, BrokerFee, which is not printed or included in the extracted version, but saved to database. You enter "Line Total" and BrokerFee manually, and Cost, which is the money that the agency pays to the companies that the agency works for, is calculated automatically.
"Total BrokerFee" is a custom field that calculates the sum of all the BrokerFee on the current invoice (receipt). This field can be added to Sales Report etc.
|Invoice Header (InvHdr)
|Invoice Body (InvBdy)
For each product, i.e. insurance in this case, you can set a default broker fee, which is loaded when you choose the product (insurance) by clicking the select-a-product icon button. You can also enter a Price as a reference for manually entering Line Total.
The link between the backend database and front end Excel invoice form is created by using names. For example, a database field named TotalBrokerFee should be named oknTotalBrokerFee on the invoice form, where "okn" is a prefix, which must be present for all the cell names recognized by Excel Invoice Manager, and "TotalBrokerFee" is just the database field name. For new columns, you add a field, such as BrokerFee to the Invoice Body (InvBdy) database table, and then name the cells on the body part of the invoice form oknBrokderFee_1, oknBrokderFee_2, oknBrokerFee_3, and so on.