## Excel Formula Help - Creating an Invoice with dropdowns (Part 1 - Create)

**Microsoft Excel** is all you need to create a great product specific invoice that will save you time and reduce the possibility of human-error.

Below, we can see a nicely formatted invoice that is so far empty of detail. Below that, (which can be on the same Excel sheet) there is also a separate table which details of stock items, in this case bottles of wine, with prices per unit at wholesale cost. What we would like to do is add a list of items from stock that, when chosen, will have their price auto-filled in the **Price** column. We would also like to be able to change the **Quantity** and see the **Line Total**, and ultimately the **Subtotal** and **Invoice Total** calculate correctly.

For this example, I have used a fictitious wine company called Posh Wines.

Firstly, on the left hand side of the invoice, we should select the area where we would like add our product items, which we have done above. We then use the **Data Validation** tool which can be found under the **Data** ribbon on the **Data Tools** pane, as seen here:

This brings up the options box. On the settings tab under **Allow**, select List and both the **Ignore blank** and **In-cell dropdown** checkbox options. The **Data** option should greyed out. The **Source** should be the list of products we would like the dropdown to offer, in this case, our list of wine - select the list and the cell range will automatically be entered with absolute references, as this example shows:

The results are clear when we return to our invoice. Each row now offers a drop down from which any item can be selected.

The stock list is also dynamic, so that if we wish to change an item, as can be seen on row 40..

..it will immediately show up in the drop down:

We now have to consider how we are going to get the price of each item to automatically appear in the **Price** column. For this, we can use a **VLOOKUP** formula which first of all looks in column **A** at the entry there for the primary part of the formula, the **lookup_value**:

The second part of the formula, as can be seen, is the **table_array**; for this, we go to our stock table and select the body of the table to provide the formula with data to examine;

Finally, we provide the column number or **col _ index _ number** where to tell **VLOOKUP** which results we are looking for (from which column in the table as they are counted from left to right). There is also either a **0** or a **1** at the end of the formula to specify whether we are looking for an exact match to the initial lookup value or a closest match, respectively. We would like an exact match, so we end on **0**. The formula should read **=VLOOKUP(A10,$A$37:$B$47,2,0)**

As we have some values in column **A** already, when we copy the formula down, the prices automatically appear under the **Price** column. However, we have some ugly error notifications in the cells below as the formula cannot find any values in column **A** for that row. We will deal with these error signs in a moment.

First, lets complete the table. We have some figures in the quantity column that we would like to use to multiply against our item prices to create a **Line Total**. This is a fairly straight forward formula, where we can simply use **=SUM(B10*C10)** or _price multiplied by quantity_, and copy down.

OK, below we can see that we now have two columns with ugly error values; let's get that fixed. First, let's remind ourself of our **VLOKKUP** formula. As we can see here, **VLOOKUP** is referencing an empty cell in **A13**. We need to have a formula that leaves the **Price** cell empty when there is no data in the corresponding column **A** cell.

We can do this by adding an **IF** function to the beginning of the formula and stating that if **A10** is empty, enter nothing in the **Price** cell. This is done with the use of inverted commas or 'speech marks' to denote 'nothing', such as **""**. So, if **""** appears in **A10**, then enter **""** in the Price cell. We amend the formula as shown, not forgetting to add an extra bracket at the end to close off the **IF** function:

When copied down to replace the present formula, our ugly error values disappear. The same can amendment can be made with the adjacent **SUM** formula in the **Line Total** column:

Our semi-automated invoice is almost complete. Now, any bottle of wine from our dynamic stock list can be selected and the price automatically appears. By adding a quantity, the total price is given in the **Line Total** column:

We then need simply to add the **Subtotal** with with another **SUM** formula and include a shipping cost. A further **SUM** formula to add the Subtotal and to the **Shipping** will provide us with a fully functioning invoice linked to our stock list.

The last thing we need to do is to print a hard copy. The best way to get this done is to select the relevant cells and go to the **Page Setup** pane on the **Page Layout** ribbon. First, select **Print Area** option and click on **Clear Print Area**. This will reveal what the print area actually is with *dotted lines* running vertically up and down the sheet:

The next step is to go to the **Scale to Fit** pane and *scale up the print area* until it crosses the dotted line:

Once it has crossed, go one step back so that the selected print area fits exactly within the vertical lines:

Follow these instructions carefully and you will be able to print the perfect invoice:

Excel4Business has Excel gurus ready to help you with a vast range of requirements, no matter which industry you are in. For more advice, get in touch.