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 3. Total comments: 203

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

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

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

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

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

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

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

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

    1. HI LAKSHMI,

      USE THIS METHOD

      =COUNTIF(A2:C30,A2)=1

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

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

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

  12. =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.

  13. 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),"")

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

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

  16. I created an excel workbook with multiple sheet that does some accounting and mathematics calculation.
    Pls how can I turn this workbook to a desktop or mobile application that runs on it own?.
    Or which programming language do I have to learn to create an app that calculates using same kind of function in Excel.
    Thanks in anticipation.

  17. I recently set up the following function in Excel, to look for a list of words in ONE cell, and if it finds any of the words listed in my formula, then 'red-flag' to say "Keyword Exclusion". Again, currently it's looking at only ONE cell (cell 'I2' in the formula below); and it's working beautifully!

    However, I now need for the formula to look at a SECOND cell as well. If it finds any of the same words listed in that second cell, then simply perform the same 'red-flag' coding. I've tried different variations of "IF(OR and AND" formulas, but it's not working.

    My current formula is as follows:

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"motor vehicle","construction","public transportation","health","family","criminal","revitalization"},I2)))>0,"Keyword Exclusion","")

    1. Hi Ignacio,

      If you want the OR logic, i.e. if any of the words is found either in I2 or, say K2, simply concatenate the two cells like this:

      =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"motor vehicle","construction","public transportation","health","family","criminal","revitalization"}, I2&K2)))>0, "Keyword Exclusion","")

      If you want the AND logic, i.e. if any of the words is found both in I2 and K2, then check each cell individually:

      =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"motor vehicle","construction","public transportation","health","family","criminal","revitalization"},I2)), --ISNUMBER(SEARCH({"motor vehicle","construction","public transportation","health","family","criminal","revitalization"},K2)))>0, "Keyword Exclusion","")

  18. Can you clarify the find and search formulas in the article are correct?
    Case-insensitive: =ISNUMBER(SEARCH($A$2:$A$6, A2))

    Case-sensitive: =ISNUMBER(FIND($A$2:$A$6, A2))

    Based on the rest of the section, should the data range listed be replaced with "AA"? Otherwise, I don't follow how the range helps with SEARCH or FIND.

    These articles are great. I always find new ways to improve my sheets.

    1. Hi!

      The correct formulas are =ISNUMBER(SEARCH("AA", A2)) and =ISNUMBER(FIND("AA", A2)) as shown in the screenshot. Don't know where those ranges came from. Sorry for the confusion and thank you - the formulas are fixed.

      1. Hi
        Using data validation, I know it is possible to do the below separately:

        1. Create a data validation drop down list (ALT + A + V + V then select list from the 'Allow' field and denote the cell range in the 'Source' field)
        2. Prevent users from entering data into one cell (e.g. cell A2) if another cell (e.g. cell A1) has not yet been populated (ALT + A + V + V then select Custom from the 'Allow' field and insert '=NOT(ISBLANK(A1))' in the 'Formula' field)

        However, I am in need of a combination of both. I need to customise a cell such that if cell A1 is not populated, then cell A2 cannot be populated but, when cell A1 has data inserted, A2 should be able to be populated but only through a drop-down list. May you assist

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)