+ Reply to Thread
Results 1 to 6 of 6

Data Validation Rule: Not Function with Exceptions to the rule

  1. #1
    Registered User
    Join Date
    06-09-2017
    Location
    Warrington, England
    MS-Off Ver
    2010
    Posts
    23

    Data Validation Rule: Not Function with Exceptions to the rule

    Hey Folks,

    Sorry if this is really simple/has already been posted I couldn't find anything related to what I wanted to do. If a post exists please let me know?

    Anyway, basically I have been asked to look at my departments shrinkage/leave sheets and combine them into one document (I have no resource planning background I'm just better than the average excel user in the company). No matter what I have tried I can't think how to get this Data Validation rule working and I think at this point I have spent to long lookng at it.

    Basically, I need to stop people booking leave if the shrinkage exceeds 34% but they still need to be able to enter the cover codes: EF, MF, LF.

    I have got the prevention bit: =NOT(D$14>34%)

    What do i need to do for this to be IF D14>34% do not allow entry UNLESS entry is "EF", "MF", "LF".

    My brain hurts and i would appreciate some help if possible?

    Thanking you in advance

    KidZest

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Data Validation Rule: Not Function with Exceptions to the rule

    Hard to say without a sample WB to play with, but perhaps something with AND?
    =and(NOT(D$14>34%),or("EF", "MF", "LF"))

    Also, have you tried
    =D$14<=34%
    ?
    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
    Registered User
    Join Date
    06-09-2017
    Location
    Warrington, England
    MS-Off Ver
    2010
    Posts
    23

    Re: Data Validation Rule: Not Function with Exceptions to the rule

    Hi FDibbins

    Thanks for the reply. Unfortunately for some reason it won't let me upload a copy of the spreadsheet.

    I tried the AND/OR statement and it didn't work, returned the error message "This formula will result in an error"

    I will do my best to give you enough information to recreate the first few columns:

    Column 1 starts at A17 with the header 'Grade' followed by 20 blank cells for the staff members grade/level.
    Column 2 starts at B17 with the Header 'Staff Name' followed by 20 blank cells for staff member details.
    Column 2 starts at C17 - same format for the team name

    Column 3 is where the tracking of absence starts. (D1:D9 will be an information box with the absence codes for staff to refer to)

    D9 - counts the total number of staff
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    D10 - counts the total number of full day booked leave and Non working day entries
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    D11 - counts the number of booked morning leave
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    D12 - counts the number of booked afternoon leave
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    D13 = Blank

    D14 - calculates shrinkage for the day
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    D15:D17 - table headers (month weekday and day numeral

    D18:D37 - where the data validation needs to go for staff entry. When D14 reaches 34% I need to be able to stop staff from entering leave codes but still allow "EF" "MF" "LF" to indicate what afternoon cover they are doing.

    I really appreciate any help or insight.

    Thanks

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,518

    Re: Data Validation Rule: Not Function with Exceptions to the rule

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    I'm a newbie with PQ, so if my solution can be improved, please let me know. Thanks

  5. #5
    Registered User
    Join Date
    06-09-2017
    Location
    Warrington, England
    MS-Off Ver
    2010
    Posts
    23

    Re: Data Validation Rule: Not Function with Exceptions to the rule

    Hi Pepe,

    I am aware of how to upload a document but unfortunately for some reason the browser would not allow me to do so (as explained) so I did the best I could.

    @FDibbins, thank you for your suggestion, you got the creative juices flowing with OR statements, and I was able to come up with something that is working, by testing for specific characters:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For anyone else who has the same issue and wants a quick explanation of what's going on in this:

    Basically, I got rid of the NOT statement - like you pointed out simpler is usually better.

    The FIND function returns a number if it matches the criteria, so adding ISNUMBER to it returns a true/false response.

    So the full Data Validation check looks to see if D14 is less than 34% if it the data is valid.
    If D14 is 34% or higher it then checks the entry for "EF" "MF" "LF" "X" "x" or "BH" with FIND, if it matches it gives a number value.
    ISNUMBER checks the result of FIND, if it gives a number result it validates the entry.

    Meaning if D14 is more than 34% and none of the allowed text is entered into the cell data validation is failed and the user cannot input the value.

    Thank you to anyone who took a look at this for me and a massive thanks to FDibbins, I had completely forgotten about the AND/OR statements and would probably still be stuck if not for your suggestion.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Data Validation Rule: Not Function with Exceptions to the rule

    I am happy that I was able to help, and get your thinking started again!!
    Nicely put together formula

    A few more suggestions...
    1. If you will only have EF, MF and LF (and no other xF) options, you could probably shorten that to this...
    =OR(E$14<=34%, ISNUMBER(FIND("?F",E18)),ISNUMBER(search("x",E18)),ISNUMBER(FIND("BH",E18)))
    FIND is case-sensitive, SEARCH is not

+ 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. Data Validation Rule for field with numbers and letters?
    By shellp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2015, 07:43 PM
  2. [SOLVED] Data Validation rule based on the result of UDF
    By hohlick in forum Excel General
    Replies: 4
    Last Post: 03-26-2015, 06:04 AM
  3. Need Data Validation rule for Blocking a cell
    By Karthik Sen in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2013, 12:14 AM
  4. [SOLVED] Data validation Rule Help
    By pammy66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-23-2012, 08:24 PM
  5. Custom Data Validation Rule
    By t0m46 in forum Excel General
    Replies: 2
    Last Post: 07-01-2010, 01:56 PM
  6. Validation Rule using data from linked tables...
    By redneck joe in forum Access Tables & Databases
    Replies: 1
    Last Post: 08-25-2009, 12:31 PM
  7. data validation-setup a validation rule
    By de049 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2008, 08:17 AM

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