Sample Invoice Template: Using Line# as Product IDs
Tip: You can find another tutorial on the same topic: Sample Invoice Template: Replacing the Product IDs with Line Numbers.
There are cases you don't need the product management feature of Excel Invoice Manager,
but want to display a Line # number in the Product ID
cells of the standard invoice template - the line # must appear automatically when you enter a
product description on a line of the invoice body.
The following tutorial shows you how to do
it in the standard invoice template included in Excel Invoice Manager.
Note: The Product Report won't work if you
customize the template in this way, since product reports are generated based on Product IDs.
- Make a backup copy of your invoice template using Windows explorer. This makes sure that
you can easily return to the currently working template if any mistake is made during customization.
- Open your template.
- Unprotect the Invoice worksheet. For information about
how to do this in Excel, see Customizing the Invoice Template.
- Switch the invoice template into design mode. For information about
how to do this in Excel, see Customizing the Invoice Template.
- Drag your mouse to select all Product ID cells.
- In Excel 2000/XP/2003, click menu Data -> Validation.
Sample invoice template: Using line number as product id - Excel 2003 (Click to enlarge)
Or in Excel 2007, go to the Data tab and choose
Data Validation from the Data Tools
group.

Sample invoice template: Using line number as product id - Excel 2007 (Click to enlarge)
- On the Data Validation window, click
Clear All. This removes all the data validations applied to
the Product ID cells. Click OK
to close the Data Validation window.
- Click the Product ID cell on the first line of the
invoice body to select it. Enter the formula =IF(oknProductName_1="","",1)
into the formula bar and press Enter.
Enter a formula (Click to enlarge)
- Repeat the above step for each line of the invoice body. For example, on the second line
you enter =IF(oknProductName_2="","",2)
for the Product ID cell; on the third line, you enter
=IF(oknProductName_3="","",3) for
the Product ID cell. Note the numbers in the formula are
replaced with the corresponding line #.
- Since the template does not utilize the product management feature of
Excel Invoice Manager, you can safely delete all
product-selection icon buttons. To delete a button, click it to select it, and then hit
the Delete key on your keyboard.
- Drag your mouse to select all the Product ID cells,
right click one of them and choose Format Cells from the menu.
- Go to the Protection tab and check the Locked option.
Lock the formula (Click to enlarge)
- You may also like to replace the original "Product ID" label with a more
meaningful one. For example, "Line #" or "#" are all suitable for this
template.
Enter the new label (Click to enlarge)
- Now you have finished your customization. Exit design mode and protect the template.
For information about how to do this in Excel, see Customizing the Invoice Template.
Tip: The free invoice template can also be customized in the same way.
Click the following links to download the template created in this tutorial:
|