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 5. Total comments: 286

  1. How to restrict equal to sign (=) in excel cell

  2. How do i lock a validation rule but can enter data in the cell

    1. Hello!
      It is not possible to block a data validation rule. It can only be deleted. More sophisticated data validation methods are possible using the VBA macro.

  3. Dear Alexander,
    Thank you for engaging to this thread once again. Despite the news are not what I was hopping for, still, excel carrying on the end result is something I kinda expected to tell you the truth.
    If it wasn't necessary to have an alternative zero value in every cell, it would be much easier. I might write all the formulas I need manually, and create separate data validation for every cell. It might take me some time, but it'll totally worth the effort.
    Thank you for your assistance. You're all great in Ablebits at what you're doing. Don't stop giving out essential and helpful hints!

  4. Hello Ablebits team,
    I'm having a hard time to figure out how to make options of a drop-down list, containing formulas, working with relative reference when I paste special with validation in another cell, without using VBA if possible.
    For example, when I copy validation from cell P31, including a list option with the formula "=MIN($D31;$K31+L31)" and paste it to cell P33, I need this formula to change automatically into "=MIN($D33;$K33+L33)".
    Could that be possible in anyway? Thank you in advance!

    1. Hello Yannis!
      When you copy cells, data validation formulas change in the same way as regular formulas. You can check it yourself.

      1. Hello Alexander and thank you for your quick response,
        I've tried it before and it seems not to be working, since the result I get by selecting this list component, is the same for every cell I paste the validation into.
        Let me clarify that it's a list containing the formula (including the value zero and the formula) and not a custom validation, where I could write the formula directly into the box while creating it, in case this detail is of any importance.
        Is there any worksheet setting I should turn on or off?

        1. Yannis,
          Thank you for the clarification. I mush have misunderstood your previous comment. I supposed you're using a custom validation formula applied to some data range and this formula is the one that doesn't get updated. If however, it is the range with different formulas you've set as the data validation source range, those formulas won't be recalculated when copying. You see, Excel uses not the formula but the end result for the validation, no matter where this validation list is.
          I hope this will help, otherwise please do not hesitate to contact me anytime.

  5. Hi,
    I am working on the excel to validate specific texts cannot be entered into the cell. Example, when user keys in Hello or HELLO or bye or NA, the cell will show specific text entered is not allowed. If user keys other text, it will be allowed to be entered into the cell. Appreciate your help to list out the formula to resolve this probllem. Thanks

  6. Column B Defines the scope of the product with two variables Yes or No e.g. Cell B1 = Yes/No
    Column C - Cell C1 has data validation of three parameters Simple/Medium/Complex.
    If the value of Cell B1 is Yes for the Scope; then only Cell C1 should get activated and the user should be able to choose the type of the parameter by clicking the drop-down arrow i.e. Simple/Medium/Complex.
    Please advise the formula or procedure to do the same.
    Best Regards

  7. Dear All I am from India, please help me I got fade of to make this 'custom data validation isuue solved. I made a table having multiple column.
    Column 'C' contains 'Items in innitial Stock', 'D' contains 'No. Of Used items', and 'E'containes 'Faulty items'.

    Now three isuue are that (1) 'E' cant be greater than either the 'C'or 'C-D' and (2) if Stock 'C' is empty/blank no Entry should be allowed in 'E'.
    And (3) if C is having 20 items and no item is used (it means 'D' is balnk), but some items foud faulty, so 'E' must not be greater than 'C'. And it must generate erro message if these condition violated.
    I tried more than 50 Formula but it satisfied only 2 conditions.
    plase help to make this formula.

    1. Hello Santosh,
      Please try the following formula for data validation for cell E3:

      =IF(AND((C3-D3)>=E3,C3>0,C3>=E3), TRUE, FALSE)

      You can learn more about Data Validation in Excel in this article on our blog:
      https://www.ablebits.com/office-addins-blog/data-validation-excel/#custom-data-validation-rules
      https://www.ablebits.com/office-addins-blog/excel-data-validation-custom-formula/

      Hope you’ll find this information helpful.

  8. Hello,
    I am working on excel and for the specific columns i need to restrict the data-entry of " ."(space and dot, consecutively). For such validation which command should i use.
    Help.

  9. I'm trying to set up a validation rule to connect the value in one column to an appropriate valid value list in another column. For example, I have a validated cell (let's say B2) where the value is validate via a list on Sheet 2 in cells A1-A3. THe valid values in the list include three names: Paul (A1), Peter (A2) and Pamela (A3). Now, on sheet 2 I have a list of Paul's prefered toys in Cells C1-C10, Peter's list is in D1-D10 and Pamela's are in E1-E10. Now, back on Sheet 1 I want to create a validation formula in Cell B3 that says...... Refer to cell B2, if the value in the cell is the value in Sheet 2, Cell A1 then select from the list in Sheet 2, between cells C1-C10. If it's A2, then select from the list in sheet 2 cells D1-D10, and so forth. Is this possible? Another way to look at this is, I want to limit choice number 2 based on choice number 1. If I'm making a selection for Pauls' Christmas present, I only want to see the things on Pauls' Christmas list. Thanks for any help you can provide.

  10. Please Help me someone. How can I use two data validation in a column? I want only 9 digits to be entered in a cell and if it's more then 9 or less then 9 it should not accepted. and also this number should be uniqe in the column

  11. To whom it may concern,

    Question,

    What is the formula for picking a month from a drop down list/pick list and enter the value for the start date(cell 1) and end date(cell 2) within that month.

    For example:

    1. I selected the month of the January from the drop down list and I will put value on cell 1 as the start date and cell 2 as the end date and will calculate the number of remaining days based on the formula =Networkdays(cell1, cell2).

    That being said, only January dates will be acceptable as this is the current value selected in the pick list.

    Thank you.

  12. How can I disallow a value from being entered into one cell based on the value in a different cell? For example, if Cell A1 has the value "ABC" then no data can be allowed to be entered in cell B1 and a rejection message displayed if possible.

  13. While doing a validation, can I have the If rule set i.e if the value in column A is selected then the appropriate list pertaining to the selected value only should appear in column B and others should be disabled.

  14. Please help me in this case: I want to create the long formula for data validation but when i type the formula it was limited character. So what could i do to complete my data validation formula?

  15. I am trying to use formula and list for data validation. For example, I have 2 columns, in the first column is the highest possible value can be chosen in the second column, I also want the user to choose from the list and not to type in their choice.

    Whenever the user chooses a value greater than the value in the first column, there should be an error message. can this be done with data validation?

  16. I want to mention in data validation, Yes,No or Other. But in other i want to put column blank as they will specify the requirement if they select other

  17. Hello there,

    I really appreciate the detailed information present on your Website i am stucked at one place in excel want to know if there is any Formula or VB Script which can help me.

    I am making a Leave management excel Template from my Office everything is perfect except the date.

    eg. if User X has applied leaves from 01-01-2019 till 15-01-2019

    I Blocked User Y to apply the Leaves from 1st Jan till 15 via Data Valitation not between field so now he can apply after (15-01-19 till 25-01-19)

    Now on the third row for User z i don't know what to do if i want to block dates for User X + User Y (i.e. User Z should not choose any dates from 01-01-2019 till 15 Jan 2019 & 15-01-2019 till 25-01-2019).

    I hope i made it clear.

    Thank you in advance.

    Regards,
    Muzamil.

  18. Hi,
    I like to have a data validation combined for the entry
    number 1 to 10 or the text "OK"
    would this be possible?
    thanks
    Cor

  19. Hi Ablebits Team,
    I checked your blog post. You're simply brilliant! I have researched a lot, and by far your responses are best.
    Here is my issue-
    I maintain a product sales workbook with different sheets for each month. Column A specifies the invoice number and has custom data validation rule for unique entries following preset case sequence and character limit(eg-ABC1234q1we).
    This works fine. Not an issue. However, i want to be able to apply the same validation rule to all sheets in the workbook to avoid duplicate entry. [Currently, I am consolidating data from each sheet to one master sheet and checking if Invoice number is unique before copying it to required sheet.]

    Is there a way around this where I can extend the validation rule between all sheets in the workbook? Plus, also consider, that new sheets maybe added to the workbook later and at that point the rule needs to be applied to include this new sheet as well.
    Please guide me with your expertise!

  20. Hi what should i do in the validation data if I only want either TCY or TTL to be written on the cells selected?

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

  22. 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.

  23. 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)

  24. 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!

  25. 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!

  26. 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

  27. 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.

  28. 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.

  29. 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)))

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

  31. 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 . . . )

  32. 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!

  33. 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 )

  34. 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.

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

  36. 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

  37. 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

  38. 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

  39. 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.

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

  41. 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?

  42. 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,

  43. 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.

  44. 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

  45. 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.

  46. 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

  47. 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.

  48. 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?

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

    1. HI LAKSHMI,

      USE THIS METHOD

      =COUNTIF(A2:C30,A2)=1

  50. 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.

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 :)