A Business Invoice with VLOOKUP

A Business Invoice with VLOOKUP

In the last section, you saw how to use VLOOKUP. We're now going to create a business invoice that relies heavily on VLOOKUP. This is intermediate excel, so don't worry too much if you don't understand it all - just skip ahead to the next section and come back to it at a later date.

But the Invoice we're going to create looks like this:

The invoice works by typing an invoice number into cell B5 (the 12343 in the image above). When you press the Enter key on your keyboard, data will be pulled from two more worksheets. The two worksheets contain Customer Data and Sales Data. The Customer's Name, Address, etc, gets pulled from a worksheet called Customer_Data, and information about what the customer bought is pulled from a worksheet called Sales_Data. All the information on the Invoice worksheet is automatically updated when you change the invoice number is cell B5.

Let's make a start, then. Create a new spreadsheet for this. If you're using Excel 2013, you'll only get one worksheet by default, so add two more at the bottom of Excel. Name the first worksheet Customer_Data, and the second one Sales_Data. The third worksheet should be changed to Invoice. Your new spreadsheet will then look like this at the bottom:

Click on your Customer_Data worksheet to select it. On the first row, enter the following headings:

CUSTOMER_NUMBER
FIRST_NAME
SURNAME
ADDRESS1
ADDRESS2
ADDRESS3
POSTCODE
PHONE
EMAIL

You can format this first row, if you like. Make the text bold, and change the background colour of the cells. Your Customer_Data worksheet will then look something like this:

Now enter some data under each heading. Enter data for the customers. Make up the details, just as we have below. The Customer number and the Phone number columns should be formatted as Text. You can leave the other Columns on General. Your worksheet will then look like this:

Now click on your Sales_Data worksheet to select it. On the first row, enter the following headings (make sure you format the columns as below, as well):

INVOICE_NUMBER General
CUSTOMER_NUMBER Text
DATE Date
DESCRIPTION General
QUANTITY General
PRICE Currency
TOTAL Currency

Format the first row however you like. It should then look like this:

Notice that the first row also has a Customer_Number column. This will contain the same number from the Customer_Data worksheet.

Now enter some data. You can use the same data as ours below, our just make up your own:

The invoice number can be anything you like. But you need to get the Customer Number from your Customer_Data worksheet. The customer numbers we used are these:

0001, 0002, 0003, 0004

This means that customer number 0001, who is called Jack Attack, has the invoice number 12340; and customer number 0002, Mary Fae, has the invoice number 12341. In other words, the same customer numbers are on both the Customer_Data worksheet and theSales_Data worksheet.

Now that we have customer and sales data set up, we can turn our attentions to the actual invoice. We'll do that in the next part. Click the link below to move on.

Posted in: Microsoft Excel,Microsoft Excel 7 (Processing Data in Excel),Tips,Trick,Tutorial,Tutorial Excel 7