O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Tutorial: Quickly Generating Invoices from a Product Catalog

The reference, lookup, and information functions really shine when you want to build worksheets that automatically carry out a series of boredom-inducing tasks. And they let you tie your information together in interesting ways. Maybe you want to determine monthly wages based on billing rates, build a parts list for an order, or create an invoice based on items that have been ordered from a product catalog. This last task, which is demonstrated in the next example, is sometimes called a dynamic invoicer . It allows sales representatives to quickly create an invoice by choosing from a catalog of company products.

The dynamic invoicer employs the following techniques:

  • Looking up information from a table with VLOOKUP( ).

  • Avoiding calculations when data isn't present with IF( ) and ISBLANK( ).

  • Referencing data from another worksheet.

  • Using absolute references to make it easier to copy formulas.

The first step is to build the table filled with your company's products. Figure 11-10 shows you the list for this example.

Here's a product catalog that a sales rep might use to build an invoice. It includes the Product ID (used for the lookup), the Model Name (used to identify the selected products), and the Price (used to calculate the total cost of an order).

Figure 11-10. Here's a product catalog that a sales rep might use to build an invoice. It includes the Product ID (used for the lookup), the Model Name (used to identify the selected products), and the Price (used to calculate the total cost of an order).

The next step is to build a worksheet that actually creates the invoices. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required