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

    Combo versus Listbox (XP SP2)

    Good Morning

    If I create a Listbox and then in another cell put =INDEX(UKPostCodes!$C$3:$C$2700,B2) the cell does what is expected and returns the correct value. I can also make it work with a normal ComboBox, why though will it not work with a ComboBox from the Control ToolBox selection?

    The reason I would prefer that it is this type of Combo is because with such a large list (2688 items) I can type in 'A' or 'S' and then have a much smaller selection using the down arrow.

    Any help really appreciated

    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: Combo versus Listbox (XP SP2)

    The list box and combo box from the Forms toolbar both return the inde of the selected item in the linked cell, so you need to use INDEX to look up the text of the selected item.

    The list box and combo box from the Control Toolbox both return the text of the selected item in the linked cell, so there is no need to use INDEX to look up the text.

  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: Combo versus Listbox (XP SP2)

    Hi Hans

    Sorry I don't quite understand, I have removed INDEX and just put =UKPostCodes!$C$3:$C$2700,B2 but it returned a #Value error

    Sorry to be a nuisance

    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: Combo versus Listbox (XP SP2)

    You don't need a formula at all! The linked cell already contains the text you need!

  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: Combo versus Listbox (XP SP2)

    Yes, the linked cell shows the Post Codes but in another cell I want it to show information based on another cell in the PostCode worksheet that is related to a specific postcode

    Perhaps the scaled down attachment will better show what I am talking about

    Thanks

    Steve
    Attached Files Attached Files
    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: Combo versus Listbox (XP SP2)

    It helps if you define a linked cell for the combo box. Let's say you use B2.
    You can then use formulas such as

    =VLOOKUP(B2,UKPostCodes!$B$3:$D$628,2,FALSE)

    and

    =VLOOKUP(B2,UKPostCodes!$B$3:$D$628,3,FALSE)

    for Depot and Notes, respectively.

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

    Re: Combo versus Listbox (XP SP2)

    Many Thanks as usual Hans

    Steve
    Cheers

    Steve

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

Posting Permissions

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