Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Variable List Details (2003)

    Loungers,
    This one has me stumped. What I need to be able to do is change the elements of a list in D4 based on a variable in B4.

    The attached file demonstrates the variables and what I need to do. It works fine in D4 but I need to be able to apply colum D.

    I hope that makes sense. Any suggestions

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Variable List Details (2003)

    In the attached version, I have created separate tables for the dropdown list in the Lookup sheet. I defined a new name Consequence2, with a dynamic formula - if you look at its definition in Insert | Name | Define, you will see that the definition changes if another cell is the active cell. One of the references in the definition is relative instead of absolute.
    I redefined Data | Validation for D4, D5 etc. to use Consequence2 instead of Consequence.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable List Details (2003)

    In addition to the file Hans created, another example of using Data Validation for Dependent Lists can be found on Debra Dagleish's site with some directions on how to set up.

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Variable List Details (2003)

    Hans, Steve

    Thankyou both for your help - very much appreciated.

    I'll give it a go!

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Variable List Details (2003)

    Hans,

    Further to your post, I need to extend the dynamic formula to include another Consequence based on another Risk type (I've extended the range for risk_type). I've looked at your dynamic formula in Consequence2 (which works fine), but I don't understand how the relationship to the Risk types and the consequences work and how to extent the range based on the new Risk type.

    Would you mind helping me out?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Variable List Details (2003)

    The options for the first risk type are in K17:K21 on the Lookup sheet, those for the second risk type in L17:L21, etc. If you expand the Risk_Type range with another item, the corresponding options should go into the next available column, i.e. into O17:O21. If you add yet another risk type, the corresponding options should go into P17:P21, etc. There is no need to modify the definition of Consequence2, only that of Risk_Type.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Variable List Details (2003)

    Thanks Hans

    I'll give it a shot

Posting Permissions

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