Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to put together a travel form - please see attached example.

    In cell A1, I have a combo box (linked to Input Range in cells A14:A16) from which I pick a city. In cells I1:J7, I have a list of approved hotels for each city. When I select a city from the combo box in cell A1, I want the hotels FOR THAT CITY ONLY to appear in the combo box in cell C1.

    I was thinking that perhaps I could achieve the desired result using Data Validation rather than combo boxes, but don't seem to be able to make this work either.

    Does anybody have any ideas as to how I could achieve this?

    Thank you so much to anybody who can help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    With the combo boxes:

    Select Insert | Name | Define...
    Enter Hotels in the Name box.
    Enter the following formula in the Refers to box:

    =OFFSET(Sheet1!$J$1,MATCH(INDEX(Sheet1!$A$14:$A$16 ,Sheet1!$A$1),Sheet1!$I$1:$I$7,0)-1,0,COUNTIF(Sheet1!$I$1:$I$7,INDEX(Sheet1!$A$14:$A $16,Sheet1!$A$1)),1)

    Right-click the second combo box.
    Select Format control from the popup menu.
    Enter =Hotels in the Input range box.
    Either clear the Cell link box or set it to another cell than A1 (that is already linked to the first combo box).
    Click OK.

    In the attached workbook, I've also included a similar solution using Data | Validation.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, you are a genius! I would never have thought of doing it like that. Thank you SO MUCH for your help.

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Basalt, Colorado, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,
    What an elegant solution!
    Linked the 'Hotel' combo box to C1.
    I noticed Using only C1 as the index for the Hotels range, you would get an error if the city was changed to one which had fewer hotels than what was already in C1.
    The following formula will always (I think) show the value DISPLAYED by the 'Hotel' combo box.
    =IF(ISERROR(INDEX(Hotels,C1,1)),INDEX(Hotels,COUNT A(Hotels),1),INDEX(Hotels,C1,1
    ))

    Dan Arbaney

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks, Dan! That's an improvement.

Posting Permissions

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