MULTI IN ONE: Business Invoice

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the previous lesson, you set up an Excel spreadsheet with three worksheets: Customer Data, Sales Data and Invoice. You have filled out the customer and sales data. It's now time to to set up the invoice.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Click on your Invoice worksheet to select it. Create the following labels:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="A basic Excel Invoice" height="325" src="http://www.homeandlearn.co.uk/excel2007/images/excel_advanced/invoice.gif" width="583" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The cell A11, under <b>Date</b>, should be formatted as a date. <b>Description</b> and <b>Quantity</b>can be left on <b>General</b>. <b>Price</b> and <b>Total</b> should be formatted as <b>Currency</b>.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
This is a very basic invoice, without any formatting. You can format it later, though, if you like. To start pulling data from the other two worksheets, we'll start with the Date, in cell A11.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So click inside cell A11. Now enter the following VLOOKUP formula:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 3)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Just to refresh your memory, inside of the round brackets of the VLOOKUP function, we have three pieces of information:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
VLOOKUP(<b>value_to_lookup</b>, <b>data_to_search</b>, <b>results_column</b>)</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
For us, the value we want to lookup is the invoice number in cell B5. The data to search can be found on the <b>Sales_Data</b> worksheet, in cells A2 to G5. (If you added more rows to your sales data then you need to change the G5 to whatever cell is the end of your data.) The column we want, the <b>Date</b> column, is column 3 (Column C).</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
After you've entered the formula, press the enter key on your keyboard. You should see<b>#N/A</b> appear. The N/A stands for Not Available. The result is not available because you haven't entered an invoice number yet. Do that now. Look at your <b>Sales_Data</b>worksheet and locate one of your invoice numbers. Return to the Invoice worksheet and enter your invoice number. Press the enter key on your keyboard and you should see a date appear in cell A11:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Using Excel VLOOKUP for an invoice date" height="354" src="http://www.homeandlearn.co.uk/excel2007/images/excel_advanced/invoice_date.gif" width="569" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The VLOOKUP for the other headings (Description, Quantity, Price, and Total) are very similar. The only thing you need to do here is to change the column number for the final position. So the VLOOKUP function cell B11 would be this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 4)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
And the VLOOKUP function cell C11 would be this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 5)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Cell D11 is this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 6)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
And cell E11 is this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=VLOOKUP(B5, Sales_Data!A2:Sales_Data!G5, 7)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Enter the VLOOKUP function for yourself in these cells. Your spreadsheet should now look like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="VLOOKUP used to get invoice details" height="353" src="http://www.homeandlearn.co.uk/excel2007/images/excel_advanced/invoice_sales_vlookup.gif" width="562" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Now change your invoice number in cell B5. When you press the enter key on your keyboard, you should find that your invoice will update all by itself:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Invoice is updated automatically" height="356" src="http://www.homeandlearn.co.uk/excel2007/images/excel_advanced/invoice_sales_vlookup2.gif" width="524" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We now need to get the customer details for the invoice. This is more complicated. It's more complicated because we need to match a customer number from the <b>Sales_Data</b>worksheet to a customer number from the <b>Customer_Data</b> worksheet. We'll see how to do it in the final part, below.</div>
</div>

Business Invoice, Part Two

In the previous lesson, you set up an Excel spreadsheet with three worksheets: Customer Data, Sales Data and Invoice. You have filled out ...