+ Reply to Thread
Results 1 to 6 of 6

Allow user to enter a specific number only if a certain dropdown is picked - error if not

  1. #1
    Registered User
    Join Date
    12-17-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft 365
    Posts
    39

    Allow user to enter a specific number only if a certain dropdown is picked - error if not

    Hello,

    I'm setting up a timesheet and I have small renewals and large renewals. If the user selects "small renewals", then I only want them to be allowed to enter "4" in their cell for a particular day (monthly calendar). If they enter in a 2 for example, they should get an error saying that they can only enter 4 (as they have selected "small renewals"). Similarly, if they pick "large renewals", I would like them to only be restricted to enter "8", with the same error message as above if they enter another number.

    Thanks so much!

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Allow user to enter a specific number only if a certain dropdown is picked - error if

    I'd recommend uploading sample workbook.

    There are multiple ways to achieve this. VBA, Data Validation formula, dependent dropdown etc. But what's best suited for your situation will depend on your workbook set up.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    12-17-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft 365
    Posts
    39

    Re: Allow user to enter a specific number only if a certain dropdown is picked - error if

    Please see attached (I hope I am within the excel forum guidelines with posting this worksheet - please advise if not).
    Column D has the type of work (small renewal etc.) and then the days with the number of hours worked to be entered. If "Small Renewals" is entered in column D, I would like the user to only be allowed to enter in 4 in the cell under days worked.
    Thank you!
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Allow user to enter a specific number only if a certain dropdown is picked - error if

    Data validation formula would be good option here.

    Select E10 to AO10 range.

    Data tab, Validation -> Custom.

    Enter following formula.
    =OR(AND($D10="Small Renewals",E10=4),AND($D10="Large Renewals",E10=8),ISERROR(FIND("Renewals",$D10)))

    Then add whatever Error Alert message you want to pop up.

    See attached sample.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-17-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft 365
    Posts
    39

    Re: Allow user to enter a specific number only if a certain dropdown is picked - error if

    I can't thank you enough! Totally appreciate this!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Allow user to enter a specific number only if a certain dropdown is picked - error if

    You are welcome

+ 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] Value can only be picked in dropdown list if a condition is met
    By adriano.r.marques in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-29-2015, 10:58 AM
  2. [SOLVED] Force user to only enter values from dropdown menu in combobox
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-05-2015, 05:15 PM
  3. enter players into Sheet 2 as they are picked
    By bigpoly60 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-29-2014, 03:43 PM
  4. Force user to enter specific alphabets in userform
    By saji in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2013, 10:22 AM
  5. Replies: 3
    Last Post: 02-26-2013, 10:45 AM
  6. Dropdown - option is picked it will return a date
    By MrJennings in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-24-2008, 12:38 PM
  7. How validation dropdown list open when cell is picked?.
    By ron in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-21-2006, 08:45 PM

Tags for this Thread

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