You will have noticed that an Excel Workbook provides 3 WorkSheets. You will need to create a new worksheet to hold the invoice(s) to send to customers. To add a new worksheet, Select Insert from the menu and then Worksheet to see one added before your current worksheet. Rename the worksheet to Invoice (or similar), press the Enter key to fix the change, then drag the label to the right of the CustList label. Note that each time you insert a new worksheet, it will always be placed to the left of the current worksheet. However, you can drag the labels around so that they can be in any order you wish.
The layout of this worksheet will try to emulate an invoice, so use will be made of a variety of border styles such that when printed it will look like a proper invoice.
One
of Excel's functions allows the current date to be displayed: NOW().
Should you want to change the way the date is displayed, use the Format>Cells...
menu (note that the NOW() function can also display the current time). The name
for the invoice was a simple link to one of the customer names in the CustList
worksheet, but the address is a little more complex.
To display the customer's address as one long string of text, three VLookUp functions were concatenated by using the & symbol - this allows data held in several cells to be joined together. Here is the function to display the address (assuming the customer's name is held in cell B6):
=VLOOKUP(B6,Client_List,2,FALSE) & ", " & VLOOKUP(B6,Client_List,3,FALSE) & ", " & VLOOKUP(B6,Client_List,4,FALSE) & "."
Note that to display additional information, e.g. punctuation and spaces, these have to be included using the & symbol. The Post Code uses one VLookUp function, as covered earlier.
The invoice is for the purchase of 22 copies of Thief of Time, 14 copies of The Last Hero and 8 copies of Neverwhere. Enter these details into the invoice, using VLookup functions for both the ISBN No and to work out the Costs (take care here when filling down the Cost formula: you may need to use absolute cell referencing! Click here if you get stuck!). Now update the StockList worksheet to show the current stock levels of these titles. Use the built in AutoSum function to show a total for the number of books ordered, show the cost of Postage and Packing (assuming 75p per book) and then the Invoice Total. Save the file.
| Previous>Customers... |