+ Reply to Thread
Results 1 to 7 of 7

Create Dynamic Data Validation List based upon Criteria

  1. #1
    Forum Contributor
    Join Date
    10-07-2011
    Location
    Plano, TX USA
    MS-Off Ver
    Excel 2013
    Posts
    141

    Create Dynamic Data Validation List based upon Criteria

    Hi Everyone -

    I am trying to create a pull down data validation list to only include a list of names that meet certain criteria.

    My attached workbook has 3 tabs, Employees, Salaried, and Hourly. I have a seperate validation pull down list in column A on the Salaried and Hourly tabs. What I am looking for is to build a dynamic list of names that meet 2 criteria. On the Salaried tab list, it would only include a list of Employees that are "Salaried" and "Active". Similar list for Hourly tab.

    Both list will need to dynamically "grow" as new employees are added to the Employee tab.

    Any help is greatly appreciated.

    Thanks,
    Jim
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-20-2011
    Location
    UK
    MS-Off Ver
    Excel 2007/10/16
    Posts
    840

    Re: Create Dynamic Data Validation List based upon Criteria

    Hi

    Have a look on the attachmen.

    Cheers
    Attached Files Attached Files
    To help you by my post? it would be nice to click on to say "Thank you".
    If you are happy with a solution to your problem?
    Click Thread Tools above your first post,
    select "Mark your thread as Solved".

  3. #3
    Forum Contributor
    Join Date
    10-07-2011
    Location
    Plano, TX USA
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Create Dynamic Data Validation List based upon Criteria

    Thanks micope21.

    I understand what you were doing and it does work. I think I did not explain what my ultimate goal was.

    I need to end up with 2 named "lists", one the list of active salaried names, second the list of active hourly names.

    See the "Goal" tab on the new attachment.

    Thanks,
    Jim
    Attached Files Attached Files

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Create Dynamic Data Validation List based upon Criteria

    hi jim, i see u already have the Names in the Name Manager. change it to these OFFSET formulas:
    Hourly_Names:
    =OFFSET(Hourly!$A$3,1,0,COUNT(Hourly!$B$4:$B$26))

    Salaried_Names:
    =OFFSET(Salaried!$A$3,1,0,COUNT(Salaried!$B$4:$B$26))

    let me know if it works

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Contributor
    Join Date
    10-07-2011
    Location
    Plano, TX USA
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Create Dynamic Data Validation List based upon Criteria

    Thanks Benishiryo

    That did work. However, the whole solution is getting too complicated. I attached a revised file.

    I created a Table named "Employees" so it can grow as new names are entered. I need to work with the actual entries in Column A rater than refer to a Range, such as "A2:A25" since I do not want to come back and change the Range value if the list grows beyond "A25".

    Hope this makes more sense.

    Thanks to any and all who might reply!

    Jim
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Lightbulb Re: Create Dynamic Data Validation List based upon Criteria

    Hello Jim
    In the attachment I've added 4 columns to your Employees Table (hidden). These columns contain formulas which create the names for the dynamic ranges from which the Data Validation dropdowns work. As they are part of the Table the columns with their formulas expand as new names are added or edited. Is this something you could work with?

    Hope this helps
    DBY
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-07-2011
    Location
    Plano, TX USA
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Create Dynamic Data Validation List based upon Criteria

    Thanks DBY. I'll give it a try

+ 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