Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Combo box interagation

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Department of Human Services
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have created a combo box in sheet using the control toolbox function and defined the list of surnames. I want to be able to create an "if" statement that looks at the entry selected in the combo box and gives the corresponding address. I have allocated a name to the combo box and used that name in an "if" statement but only get a false result. Any assitance appreciated.



  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-12 18:07, Phil J wrote:
    I have created a combo box in sheet using the control toolbox function and defined the list of surnames. I want to be able to create an "if" statement that looks at the entry selected in the combo box and gives the corresponding address. I have allocated a name to the combo box and used that name in an "if" statement but only get a false result. Any assitance appreciated.
    =VLOOKUP(E8,Addresses,2,0)

    where E8 is the LinkedCell and List is the value for ListFillRange.

    In your case List would be the list of surnames.

    Hope this helps.




    [ This Message was edited by: Aladin Akyurek on 2002-05-12 19:19 ]

    [ This Message was edited by: Aladin Akyurek on 2002-05-12 19:24 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin:
    Wouldn't the linked cell give the index number (i.e the row number of the selected list item -- in that case

    =index(Namerange,LinkedCell,2) will give surname if the surname is in column 2 of the NameRange

    Regards!

    [ This Message was edited by: Yogi Anand on 2002-05-12 20:24 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-12 20:22, Yogi Anand wrote:
    Hi Aladin:
    Wouldn't the linked cell give the index number (i.e the row number of the selected list item -- in that case

    =index(Namerange,LinkedCell,2) will give surname if the surname is in column 2 of the NameRange

    Regards!

    [ This Message was edited by: Yogi Anand on 2002-05-12 20:24 ]
    I believe the combobox is created with View|Toolbars|Control ToolBox, not Forms.

    In the latter case:

    =VLOOKUP(INDEX(Input-range,Cell-link),Addresses,2,0)

    would be more appropriate.

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I reviewed the file emailed to me by PhilJ ...

    if the ComboBox is made with View|Toolbars|ControlToolBox -- then linked cell selects the actual item from the list, and the correct formula to use is:

    =VLOOKUP(LinkedCell,List,2,0)

    if the ComboBox is made with View|Toolbars|Forms -- then the linked cell selects the index_number of the item, and the correct formula to use is:

    =INDEX(List,index_number,2)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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