Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Can anyone please help. :-)

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    =INDEX(Input range,Cell link)

    gives the value selected from the combo box.

    The formula you need then becomes:

    =IF(INDEX(Input range,Cell link)=51,E7/9,0)

    or, equivalently:

    =(INDEX(Input range,Cell link)=51)*(E7/9)

    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.

    Aladin
    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    Christchurch, New Zealand
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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)

    Aladin
    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

Posting Permissions

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