Merge & Sort Dynamic Lists

  • First off, let me say thanks to the Ozgrid community that has helped me over and over through these forums. You are phenomenal.


    Question 1: I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)


    Detail:
    I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.


    The Chicago range is defined as:
    =OFFSET(Sheet1!$A$2:$A$11,0,0,COUNTA(Sheet1!$A$2:$A$11),1)
    The Seattle range is defined as:
    =OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)


    In the boxes on the right of the lists, I just have a Data Validation List formula as:
    =Chicago and the other as =Seattle


    Notes: I've noticed that if there are spaces in the list then it does not display correctly. I've wondered if there is different way to write the formula to make it take into account spaces in the middle of the list, to make it more user friendly.




    Thanks for any ideas you have, or any help you can provide. If the attached sheet doesn't work for you, please access it here.


    Thanks,
    John

  • Re: Merge & Sort Dynamic Lists


    Quote

    Dont use CODE tags on FORMULAS.



    Use copy/paste to merge the 2 lists and sort then combined list.


    Thanks for the quick reply Dave. I apologize, I read the rules over and over, and I labeled formulas as code. It was a silly mistake, and I'm sorry.


    While your solution will work, it's hardly dynamic. The idea is that I have is to have the novice user update the list of names (which will be something more like 10 lists of hundreds of names) and still have a merged data box to select from. I've used a very simple iteration of what I need this for to reduce complexity. I thought it would be inappropriate to include an example with several thousand pieces of data. Please let me know if I can provide some more detail on this, or if there are questions that you have.


    I had previously posted a second question about sorting this combined data list, but apparently that violated a rule as well. If you do have a way to successfully and dynamically merge the lists, as well as alphabetize the merged drop down, that would solve all of my issues.


    Thanks for your help,
    John

  • Re: Merge & Sort Dynamic Lists


    Some comments and possibilities.
    In the OFFSET function:
    [frc]
    =OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)
    [/frc]
    You can use only one cell as reference
    You would have better to count the number of cells untill row ..... 1000 instead of 11 to be sure to cover all cells
    [frc]
    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000),1)
    [/frc]
    If exist an empty cell in the range B$2:$B$1000 the number of NON empty cells is different to the last row number, which explain why you don't have the complete list in the validation.
    To prepare a dynamic validation list a possibility can be:
    Use a header contain which can be used as name: Chicago Office changed to Chicago
    Cover the range of data included the header: A1 to A6
    Prepare a list: Data > List > Create List ( with header)
    Immediatly, with the same selection: Insert > Name > Create > Top Row
    You have a list named Chicago, if you clic inside the list, a star will appear at the bottom where you can enter a new value. The name (Chicago) will follow the range.
    To prepare a common list you need a macro to group all named ranges in the same column.
    Attached a file with a macro to collect all named ranged mentioned in the List Validation and install them in the name BOTH just below cell named List_BOTH.
    You can move all these list where ever you want.

    Files

    • OZ98330.zip

      (8.79 kB, downloaded 57 times, last: )

    Triumph without peril brings no glory: Just try