Comments on: Excel Data Validation: custom formulas and rules

In yesterday's tutorial we started to look at Excel Data Validation - what its purpose is, how it works, and how to use built-in rules to validate data in your worksheets. Today, we are going to take a step further and talk about the nitty-gritty aspects of custom data validation. Continue reading →

Comments page 6. Total comments: 286

  1. About my question, someone helped me and the answer is:
    =and(counta(b2)=0;countif($a$2:$a$42;a2)=1)

  2. I have 3 drop-downs (one in column A, B & C). Column A will have names, Column B will have catalog names and Column C will have item codes). I want to show multiple values in Column C based on what was selected in Columns A & B. So if I kept the name the same from column A but changed the category in Column B, then Column C will return different values based on the selection from Column B (or even A). How do I write this formula in the Data-Validation list?

    1. hello bobby,
      first you wanna a data can put one sheet and go select table you select area change in table and go to formula option select define name first select heading than put name than now per column wise heading not select only heading data select and define name heading wise and use main sheet data validation.

  3. I wanna use this formula in data validation but I dont know why it dose not answer truly
    =AND(IF(B2>0;0;1);COUNTIF($A$2:$A$42;A2)=1)

  4. Trying to create a validation where If a range of cells (in my case c5:c8) must have a value greater than 0 in order for anyone to enter any value in cell range g5:g10. I created a custom formula in cells g5:g10 that reads, =c5:c8>0 and this validation only works when a number is recorded in C5. If I enter a number in any other cell in range (c6-c8) I get the error in g5-g10. DO not know where I am going wrong. Thank you!

  5. Hello - I would like a warning to pop up if a calculated cell exceeds a limit. Is it possible? The calculated cell is creating a percent - we do not want it to exceed 100%. I can do an IF calculation in another cell, but would prefer a warning message. Thank you!

  6. f4 -> list type having two vlues - free and cash
    g4 should be based on f4 i.e if f4 is free then 100 else 0
    so i want validation that if f4= free then you can not change value of g4
    and if f4=cash then user can change value that i did with data validation custom condition
    f4"free" then you can not change value

    problem is when i change free to cash - i am able to change value suppose i made it 20
    now if i am change f4="free" it doesn't changing value of g4= 100

  7. Please I am trying to do a cell validation for text such that if the cell was ignored and left blank, It will display an error message.

    I know if you input a text and then delete it, it would display the intended error message. However, I found if you just ignore and skip the cell, no error message is displayed.

    How do I resolve this?? Like I do not want any cells omitted or ignored and file can't be closed/saved unless information is complete.

    I used the custom, unchecked "ignore blank" and formula : istext()

    1. You can't do this with data validation.
      Specifically, you can't prevent the user closing/saving the file half-way through. You could possibly do that with VBA/macro code that is triggered when the file is closed and checks the status of a checkcell.
      Without VBA, you could use Conditional Formatting to highlight the empty cells, as well as Data Validation as you describe. That would alert the user to the empty cells, but you can't stop them closing or saving the file.

      1. Thanks Chadwick. I thought as much too that I will probably need VBA to do that.

  8. Hello, I want to restrict the cell with certain alphabet and certain range of numerical value to design a mark ledger of students. I tried to do it but I couldn't. Please, help me.

    1. Hello Amit Kr. Karn
      Formula Use in Data Validation Option
      there formula
      =AND(ISnumber(A1),LEN(A1)=10)
      Number Validation 10 digit
      there formula text validation 10 text
      =AND(IStext(A1),LEN(A1)=10)

      i hope you try.

  9. Sorry I completely forgot to escape my angle brackets
    A2:-
    =IF(B2="",AND(A2>=TIME(0,0,1),A2<=TIME(11,59,59)),AND(A2>=TIME(0,0,1),A2<=B2-TIME(0,0,1)))
    B2:-
    =IF(A2="",AND(B2>=TIME(0,0,1),B2<=TIME(11,59,59)),AND(B2>=A2+TIME(0,0,1),B2<=TIME(11,59,59)))

  10. Apologies that formula again
    =IF(A2="",AND(B2>=TIME(0,0,1),B2=A2+TIME(0,0,1),B2<=TIME(11,59,59)))

  11. Something went wrong with the middle bit.
    That second cell formula:-
    B2:-
    =IF(A2="",AND(B2>=TIME(0,0,1),B2=A2+TIME(0,0,1),B2<=TIME(11,59,59)))
    (which actually goes in D2 - to be REFERRED to by B2 . . . )

  12. Hi
    Validating 2 neighboring cells (e.g. A2 & B2) to be Start and Finish times SHOULD be done with custom validation formulae . . .
    A2:-
    =IF(B2="",AND(A2>=TIME(0,0,1),A2=TIME(0,0,1),A2=TIME(0,0,1),B2=A2+TIME(0,0,1),B2<=TIME(11,59,59)))
    . . . and these work fine, while the neighbouring cell is NOT BLANK.
    When the neighboring cell is BLANK, the validation allows ANY VALUE.
    The thing is: there already IS provision for the neighboring cell being blank (it's just that Excel ignores it)
    Fortunately, the FULL validation holds - even when the neighboring cell is BLANK - if you put the above formula in e.g. cells C2 and D2 and have the validation formulae REFER to these:-
    A2:-
    =C2
    B2:-
    =D2
    Hope this helps somebody. I only discovered the problem by accident: you just ASSUME the end user will always enter a time!

  13. HI
    I NEED TO PREVENT USER TO ENTER DATA IN WHOLE COLUMN LIKE COL A
    EXCEPT IF HE ENTER ANY OF THE FOLLOWING VALUE
    ( V+D OR D OR V )

  14. Hi Guys

    I have an excel drop down list that has options in words for eg low, medium and high as the options. However once an option is selected the cell below the drop down list registers a numeric value for instance low is 1, medium is 2 and high is three. This allows the selection to drive formula in other parts of the sheet and workbook that require numeric input such as the choose formula. Any idea how this is done as am stuck.
    Thanks

    1. Hi Ross,
      On a separate sheet (not obligatory, but it's neater), create your list. Column A is the numbers 1,2,3, column B is the words.
      In the Data Validation dialogue box, set the List to point at the list you just wrote. Just the three cells with the words.
      In the cell beneath, where you want a number to appear, enter a vlookup formula. If your Data Validation cell in A1 and the number cell is A2, it would look like this:
      =VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE)
      Note that in this example, the reference is A1 is dynamic and will change if you copy the formula to other cells. The reference to the list is locked because you don't want that to change.
      Alternatively, instead of making a cell with a number and referring to that from your Choose function, you could make the vlookup part of the Choose statement.

  15. Is there any limit of characters to enter a formula in formula bar of Data validation?

  16. I copied a formula from a cell. Then I am creating a data validation for another cell. I am not able to paste the copied formula in the Setting/ allow/ custom/ formula in Data validation? please suggest how to paste a copied formula in formula bar of Data validation?

    1. Formula copy in formula bar not a cell copy dude

  17. Hi
    You formula =TIME( HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))
    is not quite correct. There should be > sign
    You article is awesome. All other formulas are working

  18. Hi, I need to restrict alphanumberic entry and not more than 200 characters in a cell, together, what formula should I use in the data validation - customer drop down.
    Please adivse. Many Thanks

  19. I have a requirement where user should not be allowed to use any excel formula like sum,+J1 for copying the cell value.Basically i want user to should be restricted for using any formal in excel.
    Please guide

    1. Hello Supriya,
      First go to special option Select formula auto select there formula you put area and use shortcut key Ctrl+1 Tab open Format Cell select tab protection select hidden option and which area not lock select locked option remove than you protection worksheet use password now you can see all password hidden not any copy your formula only show answer.

      i hope you will try.

  20. Hi plz help me to solve the validation rule-based ie, not more or less than 100

  21. I am using excel to create a form, one of the cells is for current job title. But in another section I have "New" job title, both the current and new job title are used for calculations. So what I'm trying to do is if there is nothing in the "new" cell it will equal the "current" or if they need to change the title there is a validation drop down list. Right now it seems I can only do one or the other not both. Can you help me?

  22. Hi,
    I don't want the user to enter data in a third cell (C1) as the first (A1) and second cells (B1) are empty

    I want the user must get a warning message Whenever user trying to enter it on third cell (C1) and it should not show the warning message when (A1) & (B1) are not empty.

    Can we do it with data validation?

    Thanks,

  23. Hi, I wanted to restrict data entry in a specific format which would be as follows:
    dd/mm - dd/mm

    This cell will not be in a date format. What I required the user to input date range (as text), for example, 01/11 - 15/11 or 16/12 - 31/12. Is this possible with data validation.
    Thanks for your great work.

  24. Good morning. I have a team and each member has their own alpha split and I need to be able to restrict their input into cells depending on the first name of the customers initial.
    Basically I need data validation to show an error if a user enters a name not within their split eg one user deals with customers surnames beginning with the initials A to F
    I have tried a few variations eg =countif(b3,"a-*")+countif(b3,"b-*") etc but no luck so far.
    Any help greatly appreciated. Thanks

  25. Why would a simple validation formula,=(A1+A2) fail, i.e., accepts entries that are not equal to the contents of A1 + A2??

    1. Hi John,

      If you select "Custom" in the Allow box, the formula fails indeed. However, if you allow "Whole number" equal to =A1+A2, it works. I cannot say why.

  26. Hi,

    I just wanna ask if I can use data validation if i want to restrict the text to be inputted. Example, Middle Name to be inputted but the user input Middle Initial Only. Is it possible to prompt "error" message using data validation? If yes, how? Thanks

  27. I created a list called test1. In this list are part numbers some of these PN's are specific i.e. D2301-00-03 where as other are a range represented with wildcards i.e. 100-N*** which covers a part number range from 100-N000 through 100-999. The length of the part numbers may be different and the placement of letters and numbers may be different as seen above.

    I am trying to limit data entry into a cell (A1) only if it matches this list.
    I have attempted to do this using the COUNTIF formula in DATA VALIDATION function in cell (A1). It is not working. Any ideas? Thanks.

  28. I'm trying to highlight in red number ranges that is incorrect based on criteria of first cell column for example if Cell A1 has drop down options of NSW,QLD,VIC then in B1 it returns highlights in red cell fill if the number entered in B1 isn't within criteria range for example If Cell A1=NSW then acceptable number range for B1 is 2000-2999 else it highlights the cell in B1 in red and if A1=QLD then B2 should be between 4000-4999 else it highlights that cell in red.

    Is this something doable with custom formula?

  29. HOW TO RESTRICT DUPLICATE MOBILE NO IN A SPECIFIC COLUMN

    1. HI LAKSHMI,

      USE THIS METHOD

      =COUNTIF(A2:C30,A2)=1

  30. Hello,

    IN (Excel data validation to allow text only). I want that no one will be able to enter John 1 or John22. Because in this context I can enter John 1 or John 22. But i want that if someone enter john1 by mistake than it will show the Error message.

  31. If I want to create a sheet where if one does not put any data (left it just blank) in one cell, suppose B2, he can't put any data in C2 ... How to do it?

  32. Every code start with unique value and fixed value plzz.. solve the question

  33. Can anyone tell me a quick and easy way of validating the following:
    I've got a series of questions with Yes/No validation already set up. However, if No is recorded is B2 and the next 2 questions (C2 and D2) only apply if the answer to B2 is Yes, how do I prevent someone recording a response in C2 and D2 and automatically record a N/A?

    Hope that makes sense.

  34. I am trying to format and or validate a cell in Excel 2013 to allow only a certain format. I want a six digit (customer ID) that can only start with a letter (either A, B, D, G or J)) and has exactly 5 digits following the letter. Anyone have any clue how to make this happen?

    1. Rik:
      You can use a list for the letters. This can be a drop down box in the entry cell. The five digit numbers can be in an adjacent cell.
      Using the letters create a data validation list.
      Then select a cell to create the first validation cell.
      Select Data tab and choose Validation.
      Then in the validation window in the Allow field select the List option and enter the cell range that contains your letters. Be sure to check the in-cell drop down box. Then click OK.
      The number validation is the same process except in the Validation window you select Text Length from the Allow field, Equal to from the Data field and type in the number 6 in the Length field.
      If you need the data to be in one cell then you can concatenate the cells.

  35. Hi,
    I have a requirement where a cell should allow a few specific text entries (OR) Allow decimal values only in a range (ex: -200.00 to 200.00).
    Please help me.

    Thanks

  36. Hi. Love your work!
    Is it possible to have a data validation character limit on a concatenated cell? Example : Cell C1 has formula =A1&B1. I don't want the characters in C1 to exceed 20. I've tried custom data validation with formula =LEN(A1)+LEN(B1)<=20, but it doesn't work. Any suggestions?
    Thank you

    1. Kiwi:
      I don't think you need to customize the Data Validation in Excel. Just select "Text Length" from the first drop down and then "less than or equal to" in the second field
      and enter "20" in the third field. Be sure to check or uncheck the "Ignore Blanks" check box, then enter the message to users in the message tabs and that should work.

      1. Thanks Doug
        That's what I tried first. It doesn't work, as it's looking at the text length of the formula, and not the results (I think). =A1&B1 only has 6 characters.

        1. Thanks for writing article

        2. Kiwi:
          Each time I've tested that technique it works.
          Are there a bunch of spaces or other blank characters in A1 or B1?

  37. Good day.

    I am having a strange issue with use of formula while creating a Data validation drop-down list in one of my cells.
    Formula =IF(HOVEDTABELL[FAG]="RIE"; RIETEGNINGER;RIBTEGNINGER) is tested and working if entered directly in the main worksheet. true and false conditions route to two different table on neighbouring tabs btw.
    When I try to use the same formula while creating a Data validation list, i get the "We found a problem with this formula" warning.
    Could you please suggest what could be the issue?

    Thank you.

  38. Hey folks,

    I need to make few cells required, but in case a user did not do anything on a tab (I have about ten of them in the file) I need to allow a user to save and close the file. Only if a user starts to work at THIS tab I need to force a user to fill in 3 cells within that tab.

    Could not find this anywhere, is this doable? All examples I saw invoke a VB scripting and that forces a user to fill in cells unconditionally, simply does not allow to close a file while cells not filed in. Not my case, I have to allow to close a file with these cells empty. And force a user to fill them up only if he/she starts to work at a tab with such condition.

    Thanks a lot!

  39. Hi, I want to lock some cells in a form, but would like to do it with validation. Is it possible?

    thnaks!

    1. validate for text length less than 0, obviously this will never be true, so no input is possible
      unless someone copies something there, of course

    2. I have the same problem. I tried to make the cell equal itself in the custom formula. That stops the user from changing to another text entry but is allowing numbers. Did this ever get resolved?

  40. how do I use data validation (a list or a formula) so that I can restrict the data entry to be only
    capital letters
    & (the ampersand sign)
    , (a comma)
    . (a period)

  41. =IF(M2"";ISTEXT(L2),ISNUMBER(L2)) - why not working. If any value is there in m2, the l1 should accept numbers or else text

    1. Hello,

      Please try the following formula:

      =IF(ISBLANK(M2),IF(ISTEXT(L2),T(L2),TEXT(L2,"@")),VALUE(L2))

      Hope it will help you.

  42. what sign is the use in formula for "does not equal too" like
    IF(G6="does not equal too either does not blank",VLOOKUP(G6,A6:C19,2,0),"")

    1. Hi Amit,
      The "not equal to operator" in Excel is <>

      For example:
      =IF(AND(G6<>"text", G6<>""),VLOOKUP(G6,A6:C19,2,0),"")

  43. How do I use a validation list and be able to input other data?

  44. Thanks for the instructions! How should a data validation formula look if I'm trying to specify that cells in a particular column should be formatted in either MMDDYEAR or MMDDYEAR ## format? 01, 02, etc. are added to the end of rows with the same date. (ex. 07071980, 07071980 01)

    1. Hello, Lisa,

      I can suggest you the following formulas:

      1. In case the values are entered in the MMDDYEAR format:
      =AND(NOT(ISERROR(DATE(MID(A1,5,4),LEFT(A1,2),MID(A1,3,2)))),VALUE(LEFT(A1,2))<13,VALUE(LEFT(A1,2))<>0,VALUE(MID(A1,3,2))<>0,LEN(A1)=8)

      2. In case the values are formatted as MMDDYEAR ##:
      =AND(NOT(ISERROR(DATE(MID(A1,5,4),LEFT(A1,2),MID(A1,3,2)))),VALUE(LEFT(A1,2))<13,VALUE(LEFT(A1,2))<>0,VALUE(MID(A1,3,2))<>0,LEN(A1)=11, ISNUMBER(VALUE(RIGHT(A1,2))))

      If neither of the formulas above help you, I’m afraid you need to use a special macro then. We do not cover the programming area (VBA-related questions). Please try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  45. Try use this formula for custom validation which prevent any input which start phrase "XYZ" in Cell A1: =search("xyz",A1,1)=1

  46. Thanks for the article. I was having an issue with this validation formula, =IF(IF(D5"",D5,C5)"F",H5="B",IF(IF(F5"",F5,E5)="B",H5="B",OR(H5="M", H5="B"))) and found your article. I had to remove the check for "Ignore Blanks" and check for blanks myself like this, =IF(IF(D5"",D5,C5)"F",OR(H5="B",TRIM(H5)=""),IF(IF(F5"",F5,E5)="B",or(H5="B",TRIM(H5)=""),OR(H5="M", H5="B",TRIM(H5)=""))) because D5 and F5 did contain blanks making it possible to enter anything even though the formula itself returned a 'False'. You pointed me in the right direction.

  47. What would be the formula on data validation, example the minimum amount is $25 and maximum $300 ?

  48. I've a requirement where i should NOT allow user to enter a value which starts with some phrase like "xyz". Please help me with formula.
    Thanks in Advance

  49. I need to create a excel material inventory recording sheet,all material >0 g, must record down the material expiry date.

    Example:
    If cell A1 is "0", cell A2 must key in "NA"
    If ell A1 IS ">0", cell A2 must key in "the expiry date"

    I wanted to restrict cell A2 to input date format or "NA" with reference to cell A1, can I use date validate with formula? or any suggestion?

    1. Hello Jayson,

      You can use the IF function, e.g. enter the following formula in A2:
      =IF(A1=0,"NA","the expiry date")

      If you can refer to the expiry date, e.g. if the date is in cell C1, then set the cell format of A2 to date and include the reference:
      =IF(A1=0,"NA", C1)

  50. Im having trouble adding to formula i did for custom validation.

    The formula points to several lists depending on what is in cell N2.

    I have tried this both 'Allow Custom' and 'Allow List' and receive the error:

    "There's a problem with this formula.
    Not trying to type a formula?
    When the first character is an equal(=) . . . "

    Not sure why i can update the formula by adding another list reference.
    Any ideas?

    Thanks
    Tam

    1. Hello Tam,

      Could you specify what formula you use and what result you need to get? If possible, please send a sample file to support@ablebits.com along with a link to this post and your comment. We'll do our best to assist you.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)