+ Reply to Thread
Results 1 to 9 of 9

Data Validation List Selection

  1. #1
    Registered User
    Join Date
    01-24-2015
    Location
    Winnipeg, Canada
    MS-Off Ver
    Office 2010
    Posts
    4

    Data Validation List Selection

    I want to create 15 drop-down list menu's in cells G4:G18 that all use the same source data found in column M5:M100. This source data consists of 15 min. time intervals. If I select 7:00AM in drop-down menu G4 I want the menu list in G5 to start at 7:15AM filtering out all earlier time selections. I believe I have to use an OFFSET somehow. Please help.

  2. #2
    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,936

    Re: Data Validation List Selection

    Do you want this to happen for all of them?

    DD1=7:00
    DD2=7:15
    DD3=7:30

    Or, how do you want the other DD's handled?
    Last edited by FDibbins; 01-24-2015 at 04:44 PM.
    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

  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,936

    Re: Data Validation List Selection

    In essence, the DD formula would look something like this...
    =OFFSET($M$5,MATCH($G$4,$M$5:$M$23,0),0,30,1)

    I used a small range, so adjust as needed

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Data Validation List Selection

    In other words, you want to remove from the selection list any items that have already been selected?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    01-24-2015
    Location
    Winnipeg, Canada
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Data Validation List Selection

    It's for a work-day time sheet. Workers are required to keep a log of start and finish times for each task they complete through the day. The start-time column (E4:E18) automatically populates based on the last finish-time entry one row above in the G column (E5 is a copy of G4). The user only needs to enter up to 15 finish-times with the drop-down menu's in G4:G18. After the worker enters a finish-time he no longer needs to see older time selections. I wanted to eliminate all the earlier times from the next menu selection list. i.e. a typical entry: E4 is hard-set at 12:00AM. The workers fist selection in G4 might be 7:00AM and over in I4 he selects "Off-Duty". E5 would then automatically be 7:00AM and using menu G5 he might select 8:00AM and in I5 enter "Driving". E6 would then be 8:00AM and he perhaps selects 11:30AM in G6 and "Working without driving" in I6, and so on. I want only the remaining unused times to appear in as-yet unused menu's to make it quicker to find the needed time entry (there are a total of 97) and I want the menu's to open at the top of the list displayed not the bottom.
    Last edited by gwedel; 01-25-2015 at 12:18 AM.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Data Validation List Selection

    For better understanding please attach sample excel file, remove sensitive data if any your file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Registered User
    Join Date
    01-24-2015
    Location
    Winnipeg, Canada
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Data Validation List Selection

    See attached sample file.
    Attached Files Attached Files

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Data Validation List Selection

    used named range for data validation
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-24-2015
    Location
    Winnipeg, Canada
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: Data Validation List Selection

    That's got it. Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Auto-Generate List from reference cells based on data validation list selection
    By BoundCustomDesign in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-13-2014, 11:06 AM
  2. [SOLVED] Multiple selection in Data validation list
    By anchuri_chaitanya in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-21-2013, 08:56 AM
  3. Pull Data from Multiple Columns Based On Data Validation List Selection
    By CHRISOK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2013, 11:41 PM
  4. Data validation drop down list with several selection
    By toblju in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 12:48 AM
  5. Replies: 4
    Last Post: 02-12-2012, 11:41 AM

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