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

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:

Please Login or Register  to view this content.

D10 - counts the total number of full day booked leave and Non working day entries

Formula:

Please Login or Register  to view this content.

D11 - counts the number of booked morning leave

Formula:

Please Login or Register  to view this content.

D12 - counts the number of booked afternoon leave

Formula:

Please Login or Register  to view this content.

D13 = Blank

D14 - calculates shrinkage for the day

Formula:

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

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:

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.