+ Reply to Thread
Results 1 to 3 of 3

searchable dropdown lists

  1. #1
    pblenis
    Guest

    searchable dropdown lists

    I have a two part question.
    1)how can i set up a drop down list using data validation that allows the
    user to start typing in the first couple letter/numbers of what they are
    trying to select and the list shrinks to fit those peramaters; for example if
    i had a list of 100 companies and someone typed S in the dropdown list, when
    they then clicked on the arrow only companies that start with an S appear.

    2)I have a large spreadsheet Example with 200 rows and 50 columns, the
    columns represent companies and the rows represent product numbers that are 5
    digits long. So each company/columns has a value for each row. I want the
    user to be able to be able to plug in the product number, then picking from
    the dropdown list of companies (using search in question) pick a company. I
    then want the corresponding value that corresponds to those two choices ie is
    row 2 and column 5 the i want E2 to populate into a selected cell. I know
    how to use vlookup but how would i go about using it for two variables. This
    will all be on a seperate sheet.

    Thanks


  2. #2
    L. Howard Kittle
    Guest

    Re: searchable dropdown lists

    1. Sort the list for the dropdown and put an A at the beginning of the A's,
    B at the beginning of the B's... etc throughout the list to Z.

    Type a letter in the dropdown cell and DO NOT hit enter. Click the arrow
    and you will be at the top of that letters list. (You can hit Enter, but
    then you have to re-select the dropdown cell)

    2. Most likely an INDEX - MATCH formula. Something like this from a google
    search, adapted to your ranges.

    =INDEX(C2:E4,MATCH(A1,$C$1:$E$1,0),MATCH(A2,$B$2:$B$4,0))

    HTH
    Regards,
    Howard

    "pblenis" <[email protected]> wrote in message
    news:[email protected]...
    >I have a two part question.
    > 1)how can i set up a drop down list using data validation that allows the
    > user to start typing in the first couple letter/numbers of what they are
    > trying to select and the list shrinks to fit those peramaters; for example
    > if
    > i had a list of 100 companies and someone typed S in the dropdown list,
    > when
    > they then clicked on the arrow only companies that start with an S appear.
    >
    > 2)I have a large spreadsheet Example with 200 rows and 50 columns, the
    > columns represent companies and the rows represent product numbers that
    > are 5
    > digits long. So each company/columns has a value for each row. I want
    > the
    > user to be able to be able to plug in the product number, then picking
    > from
    > the dropdown list of companies (using search in question) pick a company.
    > I
    > then want the corresponding value that corresponds to those two choices ie
    > is
    > row 2 and column 5 the i want E2 to populate into a selected cell. I know
    > how to use vlookup but how would i go about using it for two variables.
    > This
    > will all be on a seperate sheet.
    >
    > Thanks
    >




  3. #3
    Max
    Guest

    Re: searchable dropdown lists

    "pblenis" wrote:
    > I have a two part question.
    > 1)how can i set up a drop down list using data validation that allows the
    > user to start typing in the first couple letter/numbers of what they are
    > trying to select and the list shrinks to fit those paramaters; for example if
    > i had a list of 100 companies and someone typed S in the dropdown list, when
    > they then clicked on the arrow only companies that start with an S appear.


    Try Debra's page at:
    http://www.contextures.com/xlDataVal10.html
    Excel -- Data Validation -- Combo box
    (Enable autocomplete in DV via a combo box & vba code)

    > 2)I have a large spreadsheet Example with 200 rows and 50 columns,
    > the columns represent companies and
    > the rows represent product numbers that are 5 digits long.
    > So each company/columns has a value for each row. I want the
    > user to be able to be able to plug in the product number, then picking from
    > the dropdown list of companies (using search in question) pick a company. I
    > then want the corresponding value that corresponds to those two choices ie is
    > row 2 and column 5 the i want E2 to populate into a selected cell. I know
    > how to use vlookup but how would i go about using it for two variables. This
    > will all be on a seperate sheet.


    Assume source table is in Sheet1,
    companies listed in B1 across, product numbers running in A2 down

    Create 2 defined ranges to house the companies in B1 across,
    and the product numbers in A2 down

    Click Insert > Name > Define
    then enter as Names in workbook: Refers to
    CompName: =Sheet1!$1:$1

    Click Add

    then repeat for product numbers ..
    enter as Names in workbook: Refers to
    ProdNum: =Sheet1!$A:$A

    Click OK

    In Sheet2,

    Create 2 DVs to select Product numbers in col A, Companies in col B

    Select col A
    click Data > Validation
    Allow: List
    Source: =ProdNum
    Click OK

    Repeat above to create the companies' DV droplists in col B
    Source: =CompName

    Then just place in C1:
    =IF(OR(A1="",B1=""),"",OFFSET(Sheet1!$A$1,MATCH(A1,ProdNum,0)-1,MATCH(B1,CompName,0)-1))
    Copy C1 down as far as required

    Col C will return the intersection values from the table in Sheet1
    corresponding to the product numbers selected in col A & the companies
    selected in col B

    (I'm treating your 2nd question on its own <g>)
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1