Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Drop Down Lists. (Excel 97)

    Hi folks,

    I'm needing to find a 'simple' solution (i.e. would prefer not to get into VB, if I can help it) that would allow me to build into a spreadsheet a series of dropdown lists (possibly multi-columned) that when a selection is made in the first, the contents of the second drop down list changes depending on the value selected in the first, and so on. All values selected would be compiled on a seperate sheet for printing. I have looked at VLOOKUP and CHOOSE but I am now assuming the 'forms' controls will need to be used, but I can't see anything obvious to allow me to do this.

    Any ideas or similar example/sample sheets would be greatly appreciated.

    AP

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Drop Down Lists. (Excel 97)

    Hi, AP. I don't know if you are familiar with Data Validation, but what you ask can be done without VBA macros. See the example below where I want a person to pick a state in cell G2 and choose the city in cell I2. I want the cities list to change according to the state chosen in G2...

    1) Name some ranges with list content you desire.
    Example:
    A3:A4 is named "STATES"
    TX
    AL

    Cells B3:B6 is named "TX"
    Houston
    Dallas
    San Antonio
    Lubbock

    Cells C3:C6 is named "AL"
    Mobile
    Huntsville
    Dothan
    Montgomery

    Cell D3 is named "NoState"
    Pick a state in G2

    For the first drop down list (Data Validation) choose for the list source, =STATES

    For the second drop down list (Data Validation) choose for the list source, =IF(G2="TX",TX,IF(G2="AL",AL,NoState))

    I'm not sure what you meant by the second part of the question, but this should give you some ideas.

    -Lenny

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Conditional Drop Down Lists. (Excel 97)

    For the second drop down list, rather than nested IF's, you could use the INDIRECT function. In your example, the source for the list would be =INDIRECT($G$2).

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Drop Down Lists. (Excel 97)

    Good point.

    -Lenny

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Drop Down Lists. (Excel 97)

    I agree.

    However, if you have no more than 3 lists to activate at every choice point, I'd go for your IF-formulation.

    The reason: INDIRECT is a volatile function that prolongs the recalc time. If one can avoid using it, one can better do so (I will).

    Aladin
    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Drop Down Lists. (Excel 97)

    I set it up both ways (nestred IF and INDIRECT). Using the Nested IF, the user can erase the contents of G2 (State) and the drop down list for City (I2) lets user select a blank entry from the drop down list. If you set it up with INDIRECT and erase the contents of G2 (State), the user cannot access the drop down list for City in I2. A small point but may be important to designer. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Conditional Drop Down Lists. (Excel 97)

    Debra Dalgleish has a step by step example posted at:

    http://www.contextures.on.ca/xlDataVal02.html

    It is very nicely done with graphics.

    Jim Cone
    San Jose, CA


    Hyperlinks added- Mod

  8. #8
    New Lounger
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks All.

    Thanks for all your responses. They provided me with exactly what I was looking for.

    Thanks again.
    AP

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Drop Down Lists. (Excel 97)

    Jeez, that would have saved me a lot of typing.

    -Lenny

Posting Permissions

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