Using Line# as Product IDs
There are cases where 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.
(Click to enlarge)
Or in Excel 2007, go to the Data tab and choose
Data Validation from the Data Tools
group.
(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.
(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.
(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.
(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.
Click the following links to download the template created in this tutorial:
|
