+ Reply to Thread
Results 1 to 5 of 5

Data Validation list based on a table that can change

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    Mississauga, Ontario, Canada
    MS-Off Ver
    Excel 2007, soon to be 2010
    Posts
    3

    Data Validation list based on a table that can change

    Hello,

    I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:

    Column A Column B
    Office Employee
    Hamilton Emp 1
    Hamilton Emp 2
    Hamilton Emp 3
    Toronto Emp 4
    Toronto Emp 5
    Toronto Emp 6
    Toronto Emp 7
    Toronto Emp 8
    Waterloo Emp 9
    Waterloo Emp 10

    This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
    ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.

    If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.

    Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.

    So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).

    I would be ok with something like:
    Column A Column B
    Office Employee
    Hamilton Emp 1, Emp2, Emp 3
    Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
    Waterloo Emp 9, Emp 10

    and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.

    I appreciate any assistance/insight.

  2. #2
    Registered User
    Join Date
    01-06-2013
    Location
    Orange Park, Florida, USA
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: Data Validation list based on a table that can change

    KBM-

    I am going to look much deeper into what you are trying to solve for to make sure the link I am about to share with you completely meets your need.

    But I am 95% certain that what you are trying to accomplish is to create a "dependent list".

    You can find a great tutorial on it right here.

    http://www.contextures.com/xlDataVal02.html

    Cheers-
    Rick

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Data Validation list based on a table that can change

    Hi KBM and welcome to the forum

    I have some thoughts on how this could be achieved, but perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-11-2013
    Location
    Mississauga, Ontario, Canada
    MS-Off Ver
    Excel 2007, soon to be 2010
    Posts
    3

    Re: Data Validation list based on a table that can change

    Thank you Rick. I did find the dependant list page before posting but it doesn't look right. As you will see, I'm hoping to have a single list that can be modified and allow everything else to adapt accordingly. I'm looking to hand this off to managers and free them from having to modify ranges if possible. The only responsibility they need is to make sure the correct office is listed next to the users name. I'm hoping the following will help clarify what I am trying to do.

    Here is a quick workbook I threw together and attached. The main idea is to be able to edit data on Sheet4 and not have to modify any other pages.

    What I am looking for is on the Hamilton sheet, Employee Assigned column (B) I want to enable Data Validation, allow a List and have it only show users from Sheet4 that have Hamilton as their Office. The same goes for Toronto and Waterloo pages.

    The final trick is, if Employee 7 (for example) switches offices to Hamilton all I have to do is change A8 from Toronto to Hamilton and the searches that the data validation performs will place Employee 7 in the Hamilton list and Employee 7 will no longer show in the Toronto data validation.

    As for the employee list, I would also like to set it up to cover row 2 to row 21 so the list can grow and is believed to never contain more than 20 names.

    Thank you!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-11-2013
    Location
    Mississauga, Ontario, Canada
    MS-Off Ver
    Excel 2007, soon to be 2010
    Posts
    3

    Re: Data Validation list based on a table that can change

    Thanks anyways, I believe I found that this can not be done without the use of Visual Basic programming.

    Just placing a conclusion to this thread in case others are trying to do this and search through the forums.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1