You are here: HOME >>> Excel Invoice Manager

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:

.ZIP format: invoice_sample_linenumber_as_productid.zip.
.XLS format: invoice_sample_linenumber_as_productid.xls (Right-click the link and choose Save Target As in Internet Explorer).

EXCEL INVOICE MANAGER Free Download and Try

LEARN MORE Excel Invoice Manager Home What's New Difference between Editions Quick Start Introduction Setting up Company Information, Tax Invoice Template Working with Excel 2007 Installing / Upgrading Settings Workbook Taxes Misc Data Interface Database In-Cell Lookup Print Customers Creating Customer Editing Customer Customer Detail Products Creating Product Editing Product Product Detail Invoices Creating Invoice Editing Invoice Invoice Detail Payments Adding Payment Editing Payment Payment Detail Reports Generating & Printing Reports Setting Visible Columns Extracting Reports Common Tasks Using the Search Bar Using Data List Windows Importing Data into Excel Invoice Manager Networking Working with Form Properties File Free Invoice Form with Support for Incomplete Lines on Invoice Body Using Credit Note or Credit Memo Copying Report Worksheets from One Invoice Template to Another Two Versions of Customer Statement Report Custom Fields Custom Fields - Overview Custom Invoice Fields - Tutorial Customizing the Invoice Template Customizing the Invoice Template Tutorial: Creating an Invoice Template with Additional Rows Tutorial: Adding a Notes Field to Your Invoice Template Tutorial: Adding or Removing the "Same As Bill To" Button Tutorial: Adding a Clear button to the Report Worksheets Tutorial: Displaying Summary Information on Customer Statement Tutorial: Creating a Receipt Form Using Multiple-Line Address in Invoice Form Printable Invoice - Invoice Printing in a Flexible Way Adding Postcode Cells Manually to Invoice Template More Topics Registration and Services Reference

Online-Only Articles Billing Software Powered by Excel Buttons in the Template Don't Work Sample Invoice Template: Using handwriting Signature Sample Invoice Template: Using Drop-Down List Sample Invoice Template: Long Product Descriptions Sample Invoice Template: Using Line# as Product IDs How to: Password-Protecting an Invoice Template How to: Transferring Excel Invoice Manager to a New System How to: Removing Auto-created Macros from Your Invoice Template Creating a Packing Slip Template Modifying Standard Fields with Database Designer Sample Invoice Template: Replacing the Product IDs with Line Numbers Invoice Template with Support for Product-Type Specific Tax Rates Editing Paid Invoices Working with Multiple Companies or Currencies Sharing/Linking Tables between Access Databases Tutorial: Adding New Fields to Reports Sample Invoice Template: Displaying 'FREE' for Zero Values Customizing Header and Footer of Printed Invoices Customizing Invoice Template for Pre-printed Invoice Forms Charging GST Tax on Shipping Cost Invoice Form with Fixed or Static Product / Service Items

Home	Contact Us	Privacy	Link to Us
copyright © Office-Kit.com, all rights reserved