Advanced Data Validation Techniques in Excel [spreadcheats]

Posted on November 25th, 2008 in Learn Excel - 81 comments

Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet.

Problem 1: You have 2 lists of possible values and you want a way to switch between both

PS: Many thanks to Alex who proposed this idea and solution through e-mail.

You have a cell where user can enter any value from 2 lists. But you don’t want to overload the in-cell drop down list with tons of values, and rather prefer a simpler approach like this:

data-validation-switch-lists

Solution: Use an IF() formula in validation criteria

validation-criteria-if-formulaThe solution is to use an if() formula to determine which one of the two ranges should be used to validate cell contents.

  • Select the cell where you want to have this type of validation
  • Go to menu > data > validation
  • In the criteria area, select “allow” as “list”
  • In the source area, specify a formula like this: =IF($B$7="Full List",Full-list-range,Partial-list-range)

That is all, you now have a data validation list that can change its source based on user preference.

Problem 2 : You would like to change a list’s values based on what is selected in another list

data-validation-change-listsPS: Many thanks to Catherine for asking this question through email

You have a status tracking spreadsheet where each employee enters the status for each of the projects they are working on. They enter the status by first selecting the department and then selecting a project (from that department).

So how do you do this in Excel?

Solution: Use OFFSET and MATCH to determine which range to use

Remember the offset() and match() formulas we discussed in the last spreadcheats?  Assuming the list of projects for each department is in a range B10:C22 with column B having the department name and column C having the project name and the list is sorted on column B, we can use offset() and match() combination along with countif (ahem!) to determine which range to use for project cell drop-down.

  • For the department cell, we can use simple list validation with values as “Marketing, Ops, Sales, IT”
  • For project cell, go to data validation (menu > data > validation) and specify a formula like this:
    =OFFSET(C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1)
  • What is above formula doing? It is fetching a sub-range from the by finding where the first entry for the selected department is, returning x number of rows from that point, where x = no. of projects in that department.

That is all. You now have a list drop-down that changes values based on what is selected in an earlier cell.

Still having doubts?

Feel free to download this example workbook containing a tutorial on Advanced Data Validation in Excel and poke around to learn.

Subscribe to PHD to become supergood in day to day Excel in 30 days

Our spreadcheats series of posts will help you become good & productive in day to day MS Excel usage. Please subscribe to my RSS feed to get fresh new posts on excel

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

81 Responses to “Advanced Data Validation Techniques in Excel [spreadcheats]”

  1. Jorge Camoes says:

    Let me add that usually you should have those lists in a “control” or “support” sheet, creating a named range for each list. Then you could enter: If($B$7=”Full List”, range1, range2). Just a bit cleaner.

  2. Chandoo says:

    @Jorge: You are right… using a control sheet is always advisable.

    • AFP says:

      =OFFSET(C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1)

      OFFSET(reference, rows, cols, [height], [width])

      Hello everybody,

      I am trying to use the OFFSET proposed formula =OFFSET(C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1) but doing “reference” from a another sheet in the same woorkbook.

      Can you confirm that the OFFSET accept a “reference” from another sheet in the same workbook, e.g. sheet2!C9…

      Thank you in advance for your feedback.

      • Hui... says:

        @AFP
        Yes, That can be done and your format is correct
        =OFFSET(Sheet2!C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1)

        Just remember that if there are multiple values of the cell B6 in the range B10:B22 that you will get a #Value! error
        That occurs as you are trying to return a Range which will be the No of occurrences of B6 in the Range B10:B22 long
        If you want just the cell at the offset, use:
        =OFFSET(Sheet2!C9,MATCH($B$6,$B$10:$B$22,0),0,1,1)

        If you want to sum up that many cells use
        =Sum(OFFSET(Sheet2!C9,MATCH($B$6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B$6),1))

  3. Ketan says:

    @ Jorge & Chandoo == Thanx for nice idea. Without naming the range, control/list from other sheet does not work.

  4. Ketan says:

    @ Jorge & Chandoo == Ref. Problem#2
    If you make databse in following order of Area, it won’t work :
    Marketing
    Ops
    Marketing
    Sales
    Sales
    Marketing

    i.e. all the similar areas are to have one after other OR need to sort on area.

    Your comment pls !

  5. Lincoln says:

    @Ketan

    That’s why it says in the solution that “the list is sorted on column B”.

  6. Chandoo says:

    @Lincoln: thanks…

    @Ketan: you are right The list needs to be sorted as I have noted in the article.

    There are some solutions involving array formulas (shudder) etc. to overcome this, but I always try to keep these things simple so that anyone can understand and use. As such I am no good at array formulas myself and don’t venture in to them unless they are the only option.

  7. Cheryl says:

    Since we are on the topic of named ranges (well sort of) can someone tell me an easy way to rename a named range? I have a report where it would be really helpful to use a named range in my vlookup but the range varies month to month..
    And since it’s Thanksgiving, I wanted to say thanks to you Chandoo because people think I spend hours & hours researching how to do things when really most of my information comes from right here!! :)

  8. azmat says:

    I have a question….may b m asking for too much….can it work like we have on web pages…i wud illustrate it wid an example as to what actualy am lukin for….

    suppose when we select deptt “Ops” then it should remove the value currently present in project value instantly…….(if it’s not of “Ops” deptt)

  9. Chandoo says:

    @Cheryl… thank you. did you try using indirect or offset functions in the named range definition. That way even though the names stays same, you can change the range it refers to by simply changing value in a control cell. Let me know if you have trouble in doing this. I can elaborate on this.

    @Azmat: hmm.. resetting value on previous selection… I guess you can use VBA to get this effect. But you wouldnt probably want to use vba. I dont know other ways around this. Does any one know how to reset a data validation enabled field when some other cell changes?

  10. Cheryl says:

    I have not, but I know you did have a posting about those recently. I will check that out! Thank you.

  11. Karthik says:

    Have a simpler version of this solutions.
    1. Define a name range with name as “Department” and list containing “Marketing, Ops, Sales, IT”
    For Ex: In D1 put the title as Department, D2 as Marketing, D3 as Ops and so on
    2. For each of the projects define a named range with the department names.
    E1 will have the title Manufacturing, E2 has project 1,project2 …
    3. In Cell A1 use data >> Validation >> source = Department
    4. In Cell B2 just use data >> Validation >> source =INDIRECT(A1)

  12. Asif says:

    Hey in all the above examples the ‘list’ is in the same Excel file. What will I do if my ‘list’ is another Excel file?

    • Taha says:

      Hey there Asif,
      Name your list first and then in validation box type the name of your list. Let’s assume you name your list as LIST. So in validation box type as =LIST and it will work. Please reply with your reslut.
      Thanks…Taha

  13. [...] Check out Chandoo’s blog – Pointy Haired Dilbert – article ‘Advanced Data Validation’ [...]

  14. Darwin de Leon says:

    Why is it that if you extend the Range ($B$10:$B$22) to for example ($B$10:$B$33) it gives you wrong output?

  15. Chandoo says:

    @Darwin .. Are you sure you have edited the range in all places?

  16. Darwin de Leon says:

    I used your sample Excel file to test the data and extend the rows up to B33, then in Data Validation>Source I try to change the formula to this: =OFFSET(C9,MATCH($B$6,$B$10:$B$33,0),0,COUNTIF(B10:B33,$B$6),1)

    If I select Marketing, the resulting list includes Projects under Ops Area.

  17. Darwin de Leon says:

    I sent you an email with your sample data and additional rows in your gmail.

  18. Chandoo says:

    @Darwin… Did you sort the list by department name? It seems to work fine for me.

  19. Darwin de Leon says:

    Got it! I should have paid attention to the comments here. Thanks!

  20. Adnan Rafiq says:

    Can anybody tell me about calender of date option in data validation, when I wish to put any date then calender should appear on screen.

    Your reply will be highly appreciated.

    thanks and and regards,
    Adnan

  21. Chandoo says:

    @Adnan… I think you have to use a bit of VBA to show calendar control to let user enter valid dates.

  22. Amien says:

    I’ve got a few columns of data where the next column need to refer to the previous as data vaildation eg. District, Area, Area Manager, Project No. etc. if i choose a district (North) it brings up only the Areas for North but how can I go beyond that so that in a next cell I can select the Area Manager for that Area & the Project numbers for that Area

  23. Venkatesh says:

    Dear Chandoo,

    Am a Silent reader of your posts and this blog..Its quite interesting and very useful for me..
    In the above post, using Indirect(Cell reference) also works very well…The referenced cell may contain any one of the Name..

    Venkat

  24. Hui... says:

    @Adnan
    There are several examples and free pop up calendars
    a quick search of Google will find references to both
    http://www.google.com.au/search?hl=en&safe=off&q=popup+calendar+in+excel&btnG=Search&meta=&aq=f&oq=

  25. Venkatesh says:

    Amein, the same can be done with the use of Name Manager and INDIRECT function…

  26. Jon says:

    This is excellent – really helped me out. However, still a little stuck I’m afraid. I have 7 lists in total, each needs to feed off the preceeding list. Can you give me some guidance on what I need to have in the data validation cells from list 3 – 7 so it includes all of the previous entries?

    Additionally, there is some overlap in each list (i.e. I have one list titled Region, where Global is seen in more than one of the preceeding categories). Right now, when I click the drop down list I get multiple Global’s rather than just one.

    Any thoughts? I’ve been working on this for ages now so any help you can give would be great!

  27. Francis says:

    Chandoo,

    What if I have problem 1 in problem 2 above?

    To explain, If my list under marketing is too big and I only want to see only the items that are frequently used instead of all available items, how should I amend my formula?

    Help please!!!

  28. john says:

    The data has to be in the same worksheet i believe? usually a separate worksheet is used for reference. could you clarify how do you go about it? It doesnt work if you reference the data from a different sheet. the control page or something mentioned is not clear to me.

  29. ahmet says:

    Hi Chandoo,

    I am not too sure if i can use data validation with the problem that i have.

    I have a data enry of around 5000 rows in 1 sheet and i want to be able to select a person randomly after applying auto filters.

    i am not sure if this is possible.

    say for example, i want to select a name randomly which is in coumn D after filtering using other columns which shows me who is attended a specific course, meeting etc.

    your help would be much appreciated.

    Thanks

    Ahmet

  30. Chaluva says:

    Hi,
    Please help in creating Data Validation which accepts only text.

    Please Note: It should not accept text and number combination.

    Thanks
    Swamy

  31. Ananthanarayanan says:

    I have created a file with data validation. In a column there are over 75 entries in a drop down data. One has to scroll through the list to select an entry.
    Is there a way by which just by entering the 1st letter or the 1st two letter, the drop down list shownall the data with these starting letters so that the selection becomes easier.
    My thanks in advance for this guidance.

  32. Hui... says:

    @Ananthanarayanan
    Excel doesn’t have an Auto Complete facility in Data Validation.
    But with a bit of careful planning you can achieve the same result.
    Read how here:
    http://www.ozgrid.com/Excel/autocomplete-validation.htm

  33. jagmohan says:

    Question:

    If the lists are as follows what is the expected list in first dropdown?

    B C
    a aa
    a ab
    a ac
    a ad
    b ba
    b bb
    c ca
    c cb
    c cc
    c cd
    c ce
    d da
    d db
    d dc

    What I get in first dropdown is {a, a, a, a, b, b, c, c, c, c, c, d, d, d}. How to reduce it down to only {a, b, c, d}

    regards,

    Jagmohan

  34. Hui... says:

    @Jagmohan
    Have a look at some techniques here
    http://chandoo.org/wp/2010/02/02/data-validation-using-an-unsorted-column-with-duplicate-entries-as-a-source-list/
    .
    http://chandoo.org/wp/tag/unique-items/
    .
    or use an Advanced Filter
    .
    All of these techniques can be automated if required.

  35. jagmohan says:

    Hi Hui,

    Thanks. I tried to use the Pivot table technique and it worked. Further question on the same.

    Now I add few items in column H and column I (sorted again) – as follows

    Excel Information
    The lists are in Column H and I starting from row 4. F9 contains the first drop down. G9 contains the second dropdown. Pivot is located in Column M starting from row 13.

    H I
    a aa
    a ab
    a ac
    a ad
    b ba
    b bb
    c ca
    c cb
    c cc
    c cd
    c ce
    d da <- New item
    d db <- New item
    d dc <- New item

    Now is it that I need to delete the pivot table, create a new one and then use it? Or is there any technique by which I can dynamically grow the pivot table?

    Ofcourse I changed the formula in G9 to following
    offset(I4, match($F$9, offset($H:$H,0,0,counta($H:$H)-1,1),0), 0, countif(offset($H:$H,0,0,counta($H:$H)-1,1),$F$9), 1)

  36. Hui... says:

    @Jagmohan

    If the pivot table is based on a Dynamic Range, then refreshing the pivot table will add the extra items.

  37. jagmohan says:

    Now how do I check that?

  38. Hui... says:

    @Jagmohan
    Dynamic Ranges expand/defalte as the data in the range increases or is deleted
    They are added using Named Formula
    refer: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
    Once you setup a Named Formula for your range, change the pivot table to be based on that range
    As you add or remove data and update the pivot table it will adjust for the new data scenario

  39. vamique says:

    how do i do the same if there are more than 2 columns for eg , country, state, city.

  40. Bruce says:

    I want to be able to use data validation where the restricted input would be either a number greater than 0 or the text “na”. I tried using an OR statement, but it didn’t work. Not sure if I just had bad syntax or if I can’t do it that way. Any help would be appreciated.

  41. baum schausberger says:

    how to generate a list of non-repeating combinations, with some values sums off, and some values on, beside show how many evens and odds numbers.

  42. Tanvir Khan says:

    Thanks Chandoo, it’s really very helfull formula. But the formula doesn’t work if we copy and paste in the other rows and we can correct by taking out $ from $B$6. So the correct formula should be

    =OFFSET(C9,MATCH($B6,$B$10:$B$22,0),0,COUNTIF(B10:B22,$B6),1)

  43. Dan says:

    Here is my scenario I’m trying to find a solution to – any input would be greatly appreciated!

    Lets say apples oranges and pears have cost codes associated with them:

    Apples – 123456
    Oranges – 789123
    Pears – 567890

    Is there a way to see the text “Apples” in the dropdown, then once you select it the cost code ”123456″ would populate and not the Apples text?

  44. Denz says:

    In the ‘Data Validation – Change Lists’ – What if I have multiple ‘Select Area’ and ‘Select Project’? How to apply the formula in each and every cell in the column with same data list? Your speadsheet shown only one cell of ‘Select Area’ and one cell of ‘Select Project’.

    Thank you very much.

    Denz

  45. [...] Cascading Drop downs – load values in 2nd list depending first list [...]

  46. ashl says:

    Hi,

    I’ve tried this formula but my list is in another tab in my workbook and because of this i keep getting the message that  I can’t reference validation lists to other tables or worksheets. Ive tried naming my range but this isn’t working either. Please help :)

  47. Faisal says:

    I have too many columns and i want to prevent duplication. for example
    col 1    col2   col3 col4 ……………….
    xyz      123   aaa  bbb   ccc
    xyz      132   ccc   aaa   bbb
    abc      234   aaa  ccc     ddd
    abc      324   ccc    aaa   bbb

    now i want to prevent this entry
    xyz   123  ………….
    or
    abc   234  ………….

    plz help
     

    • Derek says:

      I just used pivot tables to extract the data. it will remove the duplicates. Then reference the pivot table for your drop down?

  48. Satyam Garg says:

    Hi,
    I wanted to create dependent lists. For example:
    Col A Col B Col C
    —— —— ——
    TaskA BAU Z101
    TaskA PRJ Z002
    TaskA PRJ Z003
    TaskB BAU Y403
    TaskB BAU Y407
    TaskB BAU Y412

    I need to find out what formula should I put in my data validation in Col B so that when TaskA is selected, I will only see “BAU” and “PRJ” in the dropdown list and “PRJ” should come only once. When TaskB is selected, I will only see “BAU” once. Presently with your solution I can only get to the stage where BAU will appear 3 times, if TaskB is selected.

    Thank you so much in advance for your help.

  49. Pavel says:

    Hi,
    I would like to ask if is possible to swith off Scrolling bar in pop up tab :
    I got values for example
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    and only 8 are visible in pop up window. I would like to disable this and have biger tab offering me all 10 (I am aware if bigger list i might have difficulties to pick one I need.
     
    Many thanks
     
    Pavel
     

  50. Mike says:

    Hi,
    Problem 1 didn’t work as explained.
    The data validation list only shows the false statement part and didn’t even the have Full List word showing on the List down menu.  Any advise???
    Thanks.
    Mike.

  51. Megan says:

    I am looking for a formula so that an error message is returned when incorrect data is entered into the cell.
    If the adjacent cell on the left contains the data CY+3 (or any other number)or the data IND+0 then this particular cell CAN NOT contain the data EVT (or EVT+ any number) and an error message should be returned. I then wish to copy this down the column.
    Please help! I am pretty sure this is possible. I used to be a wizz with excel formulas but I haven’t used them for almost 10 years now!
    Thanks.

  52. Pedro says:

    Hi C
    sometimes the forum looks like a wishing list ;)
    why not to add more to it…
    Change list values based on what is selected in another list?

    could you elaborate more in the case of a 3rd or a 4th list how the offset should look like, e.g..:
    List 1
    Men
    Women
    List2
    Blue
    Pink

    List3
    XL Men
    L Men
    L Women
    M Women

    so, ig I pick Men from one data list I will get from list 2 Blue and for list3 XL Men & L Men,
    what will be the best way to do it?
    =manythanks
    P

  53. Sumit says:

    Chandoo.. This is an amazing trick.. thanks a ton :)

  54. Ollie Wood says:

    Hi
    I am using the second formula using offset and match and it works fine when the list is sorted. My list is sorted but there may be instances when it is not. This formula doesn’t then return the correct values. Do you have any ideas how to get around this?

  55. Kristian says:

    In the second problem, how do one allow for the user to enter data freely in the project list?
    E.g. in my sheet the user can first choose between the areas “marketing”, “sales”, “ops” or “others”. If the user selects one of the first three he will get a new list to choose from in the “select project cell”, but if the user selects “other” he should be able to type in whatever he wants in that cell.

  56. Tom says:

    HI,
    When dealing with PROBLEM 1 i have another Problem using Data Validation. The Solution works perfekt, but activating “Circle Invalid Data” leads to the following problem:

    In SITUATION1, when selecting a value of the “Partial-list-range” the data is not recognized as invalid data. But selecting “FullList” (SITUATION2) and then a value of the “Full-list-range” the cell is circled and marked with a Data Validation Error. Saying “Restriction: Value must match one of the listed items”.

    I have googled my a** of to find anything about this problem or a workaround or sth., but without succes. I was not able to teach excel that a value of either the one or the other list is valid.
    Can anybody help me with this issue?

  57. Abhinandan says:

    Hello everyone,
    Is it possible to limit the data range depending upon value selected in drop down list.

    Suppose i have a drop down list (say in cell M1)which has 5 options, namely Refrigerator, TV, Mixer, Micro oven, Speaker.
    Now i want to limit the data entered in cell X20 depending upon the option selected.
    For example:
    For TV maximum value allowed to be entered in cell X20 = 1000$, For refrigerator 800 $ and so on….
    Is it possible to do in excel 2010? If it is possible, tell me how to do it.
    Thanks & Regards

  58. Abhinandan says:

    Thanks a ton sir.
    It’s really working.
    But there is a problem. I can still Copy paste the data above the maximum limit. Let say for TV maximum limit is 800. In some other cell (which is not Protected) i wrote 1000. Now i copied that 1000 and paste it into the validated cell. And it is showing 1000 as input.
    Can you tell me how to protect it from copy paste option?

    Thanks & Regards
    Abhi

  59. Derek says:

    So,
    I have used this validation:
    =OFFSET($T$9,MATCH($C$11,$S$10:$S$34,0),0,COUNTIF($S$10:$S$34,$C$11),1)
    I want to copy this into about 300 more cells, but I need the values for C11 to change to each subsequent cell. (C12, C13, etc)
    I have tried making Fields instead of point to a specific cell, but this brings me errors. Is there a fast way to copy the “equation” while changing cells to fit reference cells per row? Right now, I am manually changing all of them. Way too much time for what I am doing.
    Thanks.

    • Hui... says:

      @Derek
      Remove the $ from the C11
      =OFFSET($T$9,MATCH($C11,$S$10:$S$34,0),0,COUNTIF($S$10:$S$34,$C11),1)

      The $ locks that component of the cell reference and hence it doesn’t change as you copy the formula

  60. Hui says:

    Thank you sir. I will give it a try. Will let you know. I LOVE this site! You guys have a ton of great info.

  61. Derek says:

    Thanks so much! It worked. You guys at Chandoo.org are the very best! I look forward to learning more!

  62. Derek says:

    Thanks so much! It worked. You guys are the very best! I look forward to learning more!

  63. Reinard says:

    How do i get cell D6 in Sheet”Data Validation – Change Lists” to revert back to blank if the selection in cell B6 has changed?

  64. Eira says:

    I would like to validate if cell d7 contains “C” or “F”. In case “C”, cell G7 can get a value, but in case of “F”, cell G7 should be locked, or a error message shoudl pop up. can this be done with data validation?

  65. Dave says:

    Hi,

    I would like to create a ONE to MANY drop down list.

    E.g. I have a sheet with the first drop down as COUNTRY, after selecting country I would like multiple other lists to be dependant on this first selection, so BLOCK or CONTRACT lists should refer to the selection in COUNTRY and then the options reflect the chosen COUNTRY, is this possible?

    I can create 1 dependant list using the INDIRECT formula without problem but I cannot get any additional lists created that are dependent on the first selection due to not being able to define the names in the same way, i.e. select UK as country then the next list is defined by UK but I cannot create a third due to not being able to name another list as ‘UK’.

    Please can you help me out with this. (Hopefully what I have written makes sense and is clear enough…)

    Many thanks,
    Dave

  66. Anthony says:

    Hi,

    I’m used your formula for a data validation.

    I’ve just adjusted the range’s of data to reflect my lists, however it seems out of sync by 1. Instead of the dropdown returning E6:E10, it returns E7:E11.

    Thanks in advance for your help… any suggestions at all?

    =OFFSET(‘Branch List’!$E$1,MATCH($B$3,’Branch List’!H:H,0),0,COUNTIF(‘Branch List’!$H:$H,$B$3),1)

  67. shabber says:

    Super Chandoo!!

Leave a Reply