+ Reply to Thread
Results 1 to 11 of 11

Limit the number of uses of the contents of a drop down box

  1. #1
    Registered User
    Join Date
    11-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Limit the number of uses of the contents of a drop down box

    I've searched the forums and found nothing similar to my problem.

    I have a spreadsheet that I'm creating for a golfing friend and I have this scenario:
    There are just over 100 golfers that will play every Monday in the month of November 2015 . Each cell in the column next to the golfer's names has a drop down box with the available tee times. I need a way to limit the tee times so that each time is used a maximum of 4 times -- a foursome of players. After that limit has been reached the time should not appear as an option in the drop down list.

    I've seen a similar sheet but it was based on the number of uses in a row, not in a column like my problem.

    Could someone please offer me direction with this?

    Attached is a portion of the sheet. Only the Nov 2 column has been configured with the drop down box.

    Thank you. Any help with this is greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Limit the number of uses of the contents of a drop down box

    Hi mlafrance,

    This code may do what you need.

    Please Login or Register  to view this content.
    See the attached for the example with the code in it.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Limit the number of uses of the contents of a drop down box

    That's nice and succinct, MarvinP, but wouldn't you get a lot of trial-and-error messages once the slots start becoming used up?

    @mlafrance, here's one possible formula-based solution; I'm sure it can be simplified further.

    tee-off_dropdowns.xls

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Limit the number of uses of the contents of a drop down box

    Hey cyiangou,

    Cool idea to limit the dropdown list but where is the error message when the 5th person tries to sign up? Your method will allow more than 4 people to sign up on a single time. See the attached.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Limit the number of uses of the contents of a drop down box

    No error message, the slots are used up and they cannot enter any further data that could cause an error. I think I've fulfilled the spec exactly as asked. Although I'm not sure if it will work with the 10:00 AM slot because it's the last row, but that's just bulletproofing.

  6. #6
    Registered User
    Join Date
    11-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Limit the number of uses of the contents of a drop down box

    Thank you very much for your responses. I've implemented the code you gave me, MarvinP and Cyiangou. It works like a charm!
    Mike

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Limit the number of uses of the contents of a drop down box

    Hey Mike,

    I'd be making my workbook a little more like excel likes. See the attached that has lots of Dynamic Named Range and some other code to update the Pivot table in it. See if this might be better for you. This is just another idea to do what I think you want.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Limit the number of uses of the contents of a drop down box

    Hi Marvin,
    The main sheet is for reference, for the golfers to lookup their name and see what time they tee off at. So the pivot table, while cool and appreciated by you and I, doesn't serve any purpose to the end users.
    While re-working your solution I tried to get all of the references on a second, "Lookups" sheet but found it too difficult. Because I couldn't get the lookups sheet running I thought that if everything was on the first sheet that I could hide all of the columns that contain the lookup information.
    I've attached the file I'm working with. You'll see what my intentions are.
    Thanks, Marvin.
    Mike
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: Limit the number of uses of the contents of a drop down box

    Hi Mike

    Are you referring to the lookups in my contribution? I've extended it now that I've seen your full time slots.

    I've also moved the lookups to a second sheet, so the main sheet is clean except the string range references to the lookups (you can hide this row).

    tee-off_dropdowns.xlsx

    And I've added conditional formats to show:
    • When someone pastes in values (why? the point of the dynamic dropdown is to see which slots are available), exceeding the 4 count, then all golfers in that slot turn red.
    • You can now select a time slot at the top that highlights all golfers in that slot in blue.

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Limit the number of uses of the contents of a drop down box

    Here's what I did. Made a small macro that defines the named range depending on what cell is selected

    Please Login or Register  to view this content.
    Then in sheet2 C1 copied down

    =IF(COUNTIF(Toggle,B1)<4, B1, "Filled")

    I named sheet2!C1:C7 = AvailableTimes

    Then DataValidation, List = AvailableTimes
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  11. #11
    Registered User
    Join Date
    11-20-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Limit the number of uses of the contents of a drop down box

    cyiangou,
    That's exactly what I wanted.
    Thank you.
    Mike

+ 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. [SOLVED] How to limit the selection in a drop down
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2013, 04:35 PM
  2. Limit number of times a value may be selected from a drop down menu
    By searnold56 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2013, 08:54 PM
  3. How to limit the number of choices in multiple drop-down lists?
    By Stefan48 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-16-2013, 04:15 PM
  4. Replies: 0
    Last Post: 11-06-2012, 11:16 AM
  5. Using one set of Drop downs to limit another
    By SkyDesign in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-01-2010, 04:56 PM
  6. How can I get around the DV drop down list limit
    By concretetsunami in forum Excel General
    Replies: 4
    Last Post: 08-07-2009, 05:43 PM
  7. [SOLVED] limit on cell contents
    By Tina in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-29-2006, 02:10 PM
  8. Excel Solver: How do I limit the contents of a cell to 1=True or 0
    By Brian Patrick Gilbert in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 02-08-2005, 12:06 PM

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