Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation-Dynamic List (Excel 97-SR2)

    I want to apply Data VAlidation to cell (which I know how to do), however I want the list of permissable values to be dynamically built..

    I have a list of possible values; 10,20,30,40,50,60, etc, etc.. however the MAXimum value allowed in any instance will be dependent upon a value entered elsewhere. I'm looking at bandwidth calculations here, so, for the sake of simplicity assume:

    Available bandwith = 100
    Maximum permissable use = 50% (of available bandwidth)
    List of steps = 10,20,30,40,50,60,70,80,90,100,110,120,130,etc, etc....

    The actual list of permissable values in the above list is: 10,20,30,40,50 (i.e. 100 (the bandwidth) * 50% (maximum permissable use)..

    Any thoughts on how to do that?

    Regards
    Peter

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

    Re: Data Validation-Dynamic List (Excel 97-SR2)

    Example:
    The bandwidth is in cell G11.
    The maximum permissible use is in cell G12.
    The cell you want to validate is F15.
    Set the validation for F15 to custom, and enter the following formula into the formula box:

    =AND(MOD(F15,10)=0,F15<=G11*G12,F15>0)

    The first part of the AND formula checks that F15 is divisible by 10, the second part that it is less than or equal to bandwidth*max permissible use, and the third that it is positive.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation-Dynamic List (Excel 97-SR2)

    Thanks Hans, will compare yours and mine... in the interim I came up with the following..

    In the relevant cell.. Data, Validation, List and then enter the following in the source box:

    =OFFSET(A2,0,0,MATCH(B2*C2,A2:A31,1),1)

    Where:

    A2 is the start of my list
    B2 is the total available bandwidth
    C3 is the maximum permissable percentage bandwidth that can be used
    A2:A31 is the extent of my test list.

    Spreadsheet example attached for the watchers... N.B. What I'm interested in here is the principle of HOW to achieve it. Will sort out the validation latger/elsewhere.

    Edit - What isn't clear from my original post is that there may be several lists, which in themselves need to be dynamically picked (I can do that), and they won't always be in steps of 10....

    Regards
    Peter

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

    Re: Data Validation-Dynamic List (Excel 97-SR2)

    Although my idea could be adapted/expanded to take the extra conditions into account, your solution hase the advantage of presenting the user with a dropdown list, so I would stick with that.

Posting Permissions

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