Thread: Excel Formula with Combo Box (Excel 97)

1. Excel Formula with Combo Box (Excel 97)

I have a spreadsheet which contains a combo box which shows a list of numbers. This combo box is situated in cell D7. In cell G7 I would like a formula which says IF D7 = 51 then E7/9. I have tried this but it doesn't seem to work due to the number 51 not actually being stored in the cell itself.

2. Re: Excel Formula with Combo Box (Excel 97)

Right click on the combo box control.
Choose Format Control.
Then choose the Control tab.
Put the cell reference D7 in the cell link field and press OK.

This will link the result of the combo box to the spreadhsheet cell.
Note however that it will not return the value in the combo box, rather the index of the item chosen.
Ie if the second item is chosen, the number 2 will be plced in cell D7.

HIH

3. Re: Excel Formula with Combo Box (Excel 97)

Please forgive the sloppiness of the attached worksheet. I placed a combobox over cell D7. If the value of the combobox is 51, then the value in cell E7 is divided by 9.

Brent

4. Re: Excel Formula with Combo Box (Excel 97)

If you created the combo box using View|Toolbars|Forms, then you had to specify an Input range and a Cell link.

gives the value selected from the combo box.

The formula you need then becomes:

or, equivalently:

If the combo box is created using View|Toolbars|Control Toolbox and D7 is the LinkedCell, then

=(D7=51)*(E7/9)

otherwise you need to make D7 the LinkedCell for the Combo box created using Control Toolbox.

5. Re: Excel Formula with Combo Box (Excel 97)

That works great thanks.

I wonder if you can help with the next formula. I Want a formula which says =IF(INDEX(Q4:Q12,D7)=01, A, IF(INDEX(Q4:Q12,D7)=02, B,)) etc.. This will have a total of 9 agruments. Excel will only accept 7 how can I change this to accept 9?

Cheers

6. Re: Excel Formula with Combo Box (Excel 97)

( 1.) Make a 2-column list in a worksheet named, say, Admin, with 01, 02, etc in the first column and A, B, etc in the second column.

One thing though: In order to have the leading 0's, you need to format the first column as text before you enter 01, 02, etc.,
otherwise you'll lose the leading 0's.

( 2.) When ready with the 2 column list, select all its cells, go to the Name Box on the Formula Bar, type List, and hit enter.

Now use:

=VLOOKUP(INDEX(Q4:Q12,D7),List,2,0)

7. Re: Excel Formula with Combo Box (Excel 97)

If the results of the INDEX function is 1, 2, 3, 4, 5, 6, 7, 8, or 9, then you could use:

<pre>=CHOOSE(INDEX(Q4:Q12,D7),"A","B","C","D","E", "F","G","H","I")
</pre>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•