How to use Combo Box (Form Control)

flattr this!

Combo Box is one of the ways to create drop down menu in Excel. More about drop down menus is on http://excel-example.com/other-tutorial/how-to-create-drop-down-menu-in-excel-worksheet.
In this example we create a simple Order Form that calculates final prize. Combo boxes will be used for choosing goods. Here is simple list that will be source for Order Form.
simple list of goods

Solution

Let’s create a new order form on a new sheet  (Sheet2). For simplicity, it contains only 3 items to order. This is a blank template into which we’ll add combo boxes and formulas.
Empty template for Order Form

Step 1 / add combo box

Go to the Developer menu and select Combo box from “Form Controls”.
Developer menu Combo Box
Draw Combo box. Right-click on it and choose “Format Control…”

How to draw Combo box

 

In Format Control, we have to fill Input range and Cell link. Input range is source for items in combo box, so it will be list of goods. Notice that the first row of the list is empty. Cell link is the cell that will be indicate which item in the combo box was chosen. You can create some auxiliary table for this. I place it right next to the Order Form, but you can place it wherever you want (for example: to the other sheet).

Combo box Format Control

 

Now choose one of the item from Combo Box you have just created. For example item “Swim Cap”. It is the third in the goods list (including first blank row). Therefore the cell link shows number 3. In the next step we will use this number to find the price of the product. Before that we will add 2 more Combo Boxes on the Order Form.

How cell link works

 

You can add them using previous procedure or you can copy them. Anyway, you have to assign each Combo Box to different Cell link. In this, I will use cell F4, F5 and F6.

Order form with combo boxes

 

Step 2 / formulas

How to find the right price for the selected item? We can use INDEX function (from Lookup & Reference menu). Formula for the first price:

Index function arguments

 

Argument Array is range B2:B8, the prices including first blank cell. Argument Row_num is address of the Cell link. Argument Column_num is 1, because we are searching in the first column of argument Array. Now just copy this formula to other items.

Formula for the Total Price is easy:

Total price formula

 

And finally Grand Total formula:

Grand Total formula

Step 3 / hide auxiliary table

It is better to hide auxiliary table from the users. If you put it like me, just hide the column F. Right click on the letter F on the top of the column and select Hide.

How to hide column

 

Add a comment »One comment to this article

  1. I’m trying to download this file…

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress