Results 1 to 4 of 4
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Combined dynamic named ranges (Any)

    Is there any way to use two dynamic named ranges, combined, as the list source for a data validation list? (and with the two dynamic named ranges located on a different sheet from the sheet where the data validation list will be located?)

    The situation is: on sheet "Account Team", I've got a table where users enter 'Core Team Names' in one set of rows, and 'Extended Team Names' in another. To make the table flexible, we start out with just a few rows for each of the Name types, and provide a button that runs a macro to add another Core Team or Extended Team row.

    Accordingly the named ranges that the describe the Core Team Names or Extended Team Names ranges, are dynamic named ranges, as in:

    =OFFSET('Account Team'!$B$5,0,0,COUNTA('Account Team'!$B$5:$B$8),1)

    and

    =OFFSET('Account Team'!$B$11,0,0,COUNTA('Account Team'!$B$11:$B$14),1)

    Up til now, we had the result of the CoreTeamNames dynamic range, providing the source for dropdown lists that appear on several other worksheets in the workbook.
    So for example, the Data Validation list source for a dropdown on another sheet would say:

    =CoreTeamNames

    Now, I've been asked to bring the entries from both of the Core Team Names and Extended Team Names ranges, into the same data validation dropdown. Some hacking has not brought up any obvious workarounds.

    There is a discussion of a similar problem in an earlier thread here: <post#=292,275>post 292,275</post#> , but the solutions proposed there, and at the site linked to from that thread ( http://www.contextures.com/xlDataVal02.html ) don't seem help in this situation, in that they require the lists to be contiguous and the dropdown to be on the same sheet as the list sources.

    Any suggestions welcome as to how this might be done. If it makes it more helpful to visualize, I can post part of the workbook here.

    Thanks!

    Gary

  2. #2
    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: Combined dynamic named ranges (Any)

    I don't understand how yours is different than what you reference.

    The thread lists a way to make a contiguous range out the 2 non-contiguous ranges. This combined contiguous range does not have to be on the same sheet.

    Steve

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Combined dynamic named ranges (Any)

    Steve,

    Sorry, I misspoke in stating what the problem was with applying the solution from the earlier thread to my current situation - the problem was not with the need to have contiguous ranges - as you point out - but that per that thread, the lists must be the only things in the columns - again sorry for misstating that.

    The current layout I have is highly formatted, and there are a number of elements in the same columns as the lists - I won't be able to have the lists in their own columns, and still adhere to style guidelines.

    What I'm going to look at doing next though, is to bring the ranges for the CoreTeamMembers and the Extended Team Members into contiguous rows - currently they are non-contiguous, separated by two heading rows. If I move the heading row content over to a column to the left, I can make the two lists contiguous and hopefully reference them via a single dynamic named range.

    Thanks,
    Gary

  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

    Re: Combined dynamic named ranges (Any)

    The combined list can be put onto a different sheet altogether and this can be hidden. It can read the 2 other dynamic range names.

    Steve

Posting Permissions

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