Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Dynamic dropdown lists

  1. #1
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to create to columns of dropdowns in and excel spreadsheet .
    I want to have it so designed that ColA dropdowns determine what selections are available in ColB dropdowns.
    For example A:A dropdown list include CPU,Memory,Make. If you select CPU then DropDown in the corresponding B Cell will show different CPU types. If I select Memory in a different cell in ColA then the corresponding cell in ColB will have a dropdown list of Memory Selections.
    Any Ideas ?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 15:13, Nimrod wrote:
    I would like to create to columns of dropdowns in and excel spreadsheet .
    I want to have it so designed that ColA dropdowns determine what selections are available in ColB dropdowns.
    For example A:A dropdown list include CPU,Memory,Make. If you select CPU then DropDown in the corresponding B Cell will show different CPU types. If I select Memory in a different cell in ColA then the corresponding cell in ColB will have a dropdown list of Memory Selections.
    Any Ideas ?
    I'll spare you details as I'm sure you're capable !

    list out your lower-level lists and give them all defined named ranges each, named per your upper-level selection possibilities

    so your list of memory selections is called "memoery" as it's an option in yoru first list... etc etc etc

    then in the cell you want your second dropdown, data validate it as "from list" referring to =indirect(a1).... where a1 was the result of your first dropdown

    makes sense ?


    :: Pharma Z - Family drugstore ::

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yesss ! Got it , Tried it .... perfect !
    Very compact . Thx

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Australia
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Um, could you expand on this for me please?
    Does this work in excel97?

    Thanks,
    Juzzy


  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi J:
    What part of this would you like elaborated. How to set it up?

  6. #6
    Board Regular Bruno's Avatar
    Join Date
    Feb 2002
    Location
    Flanders
    Posts
    484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    jmord70,

    Look also at this previous posting :

    http://www.mrexcel.com/board/viewtop...pic=40&forum=2

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 18:50, jmord70 wrote:
    Does this work in excel97?
    Juzzy,

    yep, I use 97

  8. #8
    New Member
    Join Date
    May 2002
    Location
    Australia
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes please Nimrod...that would be great!

  9. #9
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay J ... here are the details to set it up .. enjoy

    For an example let’s create two drop-downs with B1 being dependent on A1. That is to say what is displayed in A1 will determine what is available in the B1 drop-down list.

    Step 1: Create 4 lists on Sheet2
    A1=CPU
    A2=Memory
    A3=HDSZ

    B1=P2
    B2=P3
    B3=P4

    C1=128
    C2=256
    C3=512

    D1=Gigbyte
    D2=Terabyte

    Step 2: Highlight each list and name it.
    ( naming a list is done with the “Insert” …”name” … toolbar command
    2.a. The A:A list can be called anything ..this time lets call it “overview”
    2.b. The B:B list Must be called “CPU”
    2.c. The C:C list Must be called “Memory”
    2.d. The D:D list must be called “HDSZ”

    Step3: Go to Sheet1 to make the two drop-downs

    Step4: Select sheet1!A1 and select “Data”… “validation” from toolbar commands

    Step5: in the validation window the “Allow:” drop down = “lists” and in Source textbx type: “=overview” …. Press OK

    Step6: To make the B1 drop down repeat step 4 & 5 ACCEPT in Source textbx type: “=INDIRECT(A1)”

    Now Your Done !!!!!!!

  10. #10
    New Member
    Join Date
    May 2002
    Location
    Australia
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Nimrod,

    Forgot to post a thankyou for this one so...

    Thanks!!!

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
  •