Adding a combo box to an Excel worksheet

Adding combo box control to Excel worksheet makes data entry easier and interesting: Let's say we take a loan against purchase of our dream car. We have different models to choose from and these models have different prices. We would use the 'pmt' function to calculate our monthly payments. In the 'PMT' function we need to enter the following parameters: Loan Amount, Period of loan and Interest Rate on the loan. In our case we wish to make the data entry of the model and immediately get the related price in an appropriate cell of the Excel worksheet, preferrably next to the loan amount. We achieve this as follows with the combo box control:

• Type the list of the models under the header 'CAR'
• Type the related prices under the header 'PRICE'
• We select a cell in our table and press Ctrl+Shift+* to select the whole table
• On the Insert menu select Name, Create. Select Top Row check box, clear any other checked boxes and click OK. This action creates two named ranges: 'CAR' for the list of cars and 'PRICE' for the related prices.
• From the view menu, select Toolbars and display 'forms'.
• Click the combo box button on the forms toolbar. Go to an appropriate cell and drag and draw a combo button box.
• Right-click the combo box, select 'format control...' to display a dialog box.
• In the input range type 'CAR', in the cell link type an appropriate cell address and then click OK
• Press Esc to deselect the combo box.
• Click the arrow of the combo box to see a list of car models. Select a model of your choice. You will see a number displayed in the 'cell link'.

The combo box button has been linked to the car list. Now you need to type a simple formula in cell, let's say, 'C3' to retrieve the price from the named list range 'PRICE'. The formula you apply is '=INDEX(Price,Cell link Address)'.

The Excel training video demonstrates the concept in detail.You will need the Windows Media Player to view the movie.

In the next two videos we will demonstrate how to add a spinner button to input service charges and a scroll bar to input and change the interest rate to see how we can get a monthly payment option that suits our pocket!