"Re-Displaying" Named Ranges

  • Hello there,

    I'm trying to automate a spreadsheet that will display a Weekend Duty Roster.

    Besides "DATE", I've got 3 Columns:

    • Coordinator
    • Tech 1
    • Tech 2

    Each column has a list of names. Each list has a different number of people. There are more Coordinators than Techs ... e.g.- Techs are On Call more often than Coordinators.

    I know I can put the 3 lists on a seperate page, and reference them somehow with formulas. But how can I create formulas that automatically "know" how long each list is, and adjust if names are added/deleted?


  • Re: Multiple "For ... Each" Loops


    Check out the link after my name. It'll tell you how to make dynamic named ranges which will do exactly what you are looking for.

  • Re: Multiple "For ... Each" Loops

    Also see the spreadsheet I posted (a few minutes ago) in response to the posting "CPI Indexing- Formula".

    Once you've opened the spreadsheet ..go Insert, Name, Define and look at the code associated with "CPI_Table".

    Robert Hind
    Perth - Western Australia

  • Re: Multiple "For ... Each" Loops

    Thanks for the instructions on setting up the "auto-sizing" Named Ranges on my Config page.

    Now, ... on the page that displays the columns:


    what formula would go in the Coordinator column that would cycle through the names in the "Coordinator" Named Range? I can work out the other 2 columns if you can help me with the first one.

    Thanks again,
    David Baldock

  • Re: Multiple "For ... Each" Loops

    Ok, here's something I tried that sort-of works.

    On the Config sheet, the named-range that has Coordinator names in it is named Coordinator.
    On the page that displays the Duty Roster, in the Coordinator column, I put "=Coordinator" in all the cells.
    The list of names from the named-range shows up in the column, but only once. After the last name, I get the #VALUE error.
    I need the names to keep repeating down the column until the last Date row.
    What formula would do this?

    David Baldock

  • Re: Multiple "For ... Each" Loops

    I'm not quite sure what you're trying to do...but it sounds like you want to choose from a list of coordinators against each job.

    If so have you tried using (in the Coordinators column) using the data validation function (on the Toolbar > Data, Validation). Select allow List, then show the reference list as being =Coordinators (refering to the named range).

    Is this heading in the right direction.

    Robert Hind
    Perth - Western Australia

  • Re: Multiple "For ... Each" Loops

    Robert, thanks for the response.

    What I'm trying to do is this:

    There are three lists of people, each different lengths.
    COORDINATOR __ TECH1 __ Tech2
    Coord #1 _________ T1 #1 ___ T2 #1
    Coord #2 _________ T1 #2 ___ T2 #2
    Coord #3 _________ T1 #3 ___ T2 #3
    Coord #4 _________ T1 #4 ___ T2 #4
    Coord #5 _________ T1 #5
    Coord #6 _________ T1 #6
    Coord #7 _________ T1 #7
    Coord #8
    Coord #9

    The lists are in auto-resizing named ranges on the Config sheet.

    The Weekend Duty Roster sheet should look like this:

    11-Feb-05 __ Coord #1 _______ T1 #1 ____ T2 #1
    18-Feb-05 __ Coord #2 _______ T1 #2 ____ T2 #2
    25-Feb-05 __ Coord #3 _______ T1 #3 ____ T2 #3
    04-Mar-05 __ Coord #4 _______ T1 #4 ____ T2 #4
    11-Mar-05 __ Coord #5 _______ T1 #5 ____ T2 #1
    18-Mar-05 __ Coord #6 _______ T1 #6 ____ T2 #2
    25-Mar-05 __ Coord #7 _______ T1 #7 ____ T2 #3
    01-Apr-05 __ Coord #8 _______ T1 #1 ____ T2 #4
    08-Apr-05 __ Coord #9 _______ T1 #2 ____ T2 #1
    15-Apr-05 __ Coord #1 _______ T1 #3 ____ T2 #2
    22-Apr-05 __ Coord #2 _______ T1 #4 ____ T2 #3

    I can get the lists to display one iteration each. I'm just having trouble getting the lists to repeat (the bold-italics part) on the Display sheet.

    Thanks for any help,
    David Baldock

  • "Re-Displaying" Named Ranges

    *BUMP* (added 20:15 CST, 11-Feb-05)

    I still haven't figured out how to get Named Ranges to display over and over (multiple times) as shown above.
    If anyone can explain how to do this, I'd sure appreciate it.

    David Baldock

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!