Section 11.4. Tutorial: Quickly Generating Invoices from a Product Catalog

11.4. 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.

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. You should probably include space for entering customer information, followed by a large area for the list of items that have been ordered, as shown in Figure 11-11.

The best approach in this example is to retrieve an item from the product catalog based on the product ID; this prevents spreadsheet users from having to type out full product names . Therefore, a lookup function needs to be written to scan the product table for a matching ID. And since the invoice needs to record the product name and its price, you need two lookup functions. Here's the lookup that retrieves the price for the first item in the invoice:

 =VLOOKUP(A12, Products!$A:$D, 3, FALSE) 

The lookup for the model name is the same, but the column offset is 2 instead of 3:

 =VLOOKUP(A12, Products!$A:$D, 2, FALSE) 

The reference to the table of products is preceded by the word Products and an exclamation mark. That's because the products table isn't on the invoice worksheet. Instead, it's in a different worksheet in the same workbook named Products. (Remember, in order to perform a calculation with data in another worksheet, you just need to preface your cell reference with the name of the worksheet, followed by the exclamation mark. For a refresher on how functions interact across worksheets and workbook, see Section 7.3.3.)

Figure 11-11. To add items to this invoice, the sales rep simply types in the product ID and quantity. The lookup functions retrieve the model name and unit price, and then determine the total price.


You'll also notice that this function uses a mix of absolute and relative references. The reference for the product ID is relative (A12) because each line item in the invoice has its own product ID, which is what the sales rep enters to start filling up the purchase order. The product table is a range made up of two absolute references ( $A$2:$D$42 ). This ensures that as you copy the formula down to subsequent rows on the invoice table, each copy will refer to the same product catalog.

As written, the function still suffers from one problem. If you copy the formula to all the available rows in the invoice, you'll notice that the value #N/A appears in each row where you haven't entered a product ID. This error message occurs because the VLOOKUP( ) function attempts to perform a lookup for a product with a product ID of 0, which doesn't exist. The #N/A error message is a significant problem because it prevents you from calculating the total cost of the order with the SUM( ) function. If the SUM( ) function attempts to add together a range of cells that includes an #N/A value, it'll simply return the #N/A error code.

To solve this problem, you must use conditional logic to test if a product ID has been entered. If it hasn't, you can simply put a blank value in the price column. Here's the corrected formula:

 =IF(ISBLANK(A12), 0, VLOOKUP(A12, Products!$A:$D, 3, FALSE)) 

You can now build an invoice in seconds, just by typing in a few product ID numbers in the leftmost column. The products will appear automatically, and you can calculate the overall total with a simple formula that uses the SUM( ) function:

 =SUM(E12:E100) 


Note: The only limitation that remains in this dynamic invoicer is that the sales rep must know which product ID codes to use (or must look them up on the first worksheet). An even more impressive worksheet might automate this process with some sort of list box control and VBA (Visual Basic for Applications) code. You'll learn more about these tools in Chapter 26.


Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net