Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Data validation with a non-contiguous named range

    Is there a straightforward way around Excel's inability to use a non-contiguous named range for Data Validation ?

    Thanks

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Here's a possible solution using a custom function in VBA, a Named Range, and Data Validation. The named range is required as you can't directly put a Function name into a custom data validation box. Hope this helps.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  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
    The most "straightforward" method I can see is to make the list contiguous.

    Barring that, another method is to create a contiguous list from the non-contiguous range then use this range as the validation list.

    Another possibility would be to not use validation but use a combobox whose elements are read at runtime from the non-contiguous list and added to the combobox.

    Steve

  4. #4
    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
    RetiredGeek's code makes the validation case-sensitive. If that is not desired, change the line of code to:
    If Ucase(oCntr.Value) = Ucase(zToVerify) Then

    and the match will be insensitive to case.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks !
    How can I put RetiredGeek's code into a dropdown ? I should have said its really a lookup that I'm seeking, which I lazily referred to as validation.

  6. #6
    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
    I don't think it can be, it is just used to validate.

    For a dropdown list you will either have to create an intermediate contiguous range to use as the dropdown which somehow reads the non-contiguous range with a formula (which will depend on your exact circumstances) or to not use validation but instead uses a combobox which has the items added at runtime based on the non-contiguous range.

    By far the simplest would be just to use a contiguous range to not have to devise the formula for an intermediate columns or not require the macro and the more complex combobox. The combobox approach not only would require a macro but the macro would essentially disable the UNDO feature on that sheet which many people may seen as a large issue.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks.
    At least I know I'm not missing something obvious.
    Now I shall simply write some code which runs at startup and creates the contiguous range.

Posting Permissions

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