Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Control Toolbox ComboBox (2003 XP)

    Good afternoon

    I usually use the forms ComboBox and if I wanted to show other information using its result, I use =INDEX($B$4:$B$2771,A1) I have however inherited some workbooks where the ComboBoxes have been added using the Control Toolbox and I cannot seem to use the same thing, do you have to use a different kind of formula with Control Toolbox items?

    Thanks

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Control Toolbox ComboBox (2003 XP)

    The linked cell of a combo box from the Forms toolbar shows the index of the selected item: 1=first item etc.
    The linked cell of a combo box from the Control Toolbox shows the selected item itself, so there is no need to use INDEX, you can refer to the linked cell directly.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Toolbox ComboBox (2003 XP)

    Thanks Hans

    I have taken out INDEX and put =$B$4:$B$2771,A1 and get a #Value error, likewise if I try it the other way around =A1,$B$4:$B$2771, sorry to be thick!!

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Control Toolbox ComboBox (2003 XP)

    Just

    =A1

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Toolbox ComboBox (2003 XP)

    Mm? Me being obtuse again I suspect

    I have the Control toolbox Combo linked to cell A1 which in turn takes its values from $A$4:$A$2771 if I type into cell B1 = A1 it duplicates whatever I have selected in the Combo, the infromation I want to display in B1 though is in $B$4:$B$2771 so that when I select say the 5th item in the Combo 'A8' the corrosponding information stored in B8 is shown in B1

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Control Toolbox ComboBox (2003 XP)

    Sorry, I didn't read your request carefully.

    One solution would be to replace the combo boxes from the Control Toolbox with combo boxes from the Forms toolbar.

    If you prefer to keep the ones from the Control Toolbox, you can use a formula like this:

    =VLOOKUP(A1,$A$4:$B$2771,2,FALSE)

    This formula looks up the value of the linked cell in the first column of A4:B2771. It returns the corresponding value from the second column. The FALSE indicates that you're looking for an exact match.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Control Toolbox ComboBox (2003 XP)

    =index($B$4:$B$2771, match(A1,$A$4:$A$2771,0))

    Steve

Posting Permissions

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