Limit Survey Choices With Data Validation In #Excel

Here’s how you can control the number of choices on surveys and questionnaires using Data Validation In Excel:

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here we have three different questions we want our users to answer. For Favorite Vacation Spots we want to limit the choices to three. For Skiing or Swimming, we want them to choose only one, and for Preferred Accommodations, we want them to either select two from the list, or enter something in Other, but not both:

dv1

In all three cases, we are going to use Data Validation, which can be found on the Data tab of the ribbon, in the Data Tools group. In all three cases, we will highlight the cells where we want to insert the formula, then click on Data Validation, and choose Custom under Allow, then insert our formula in the Formula box:

dv2

For the Favorite Vacation Spots, we will use this formula:

=COUNTA($D$4:$D$18)< =3For the Skiing or Swimming, we will insert this formula:=COUNTA($H$5:$H$6)< =1For the Preferred Accommodations, we will use this formula:=OR(AND(COUNTA($J$10:$J$16)<=2,COUNTA($G$18)=0),AND(COUNTA($G$18)=1,COUNTA($J$10:$J$16)=0))The first two are pretty straight forward. In both cases, we are testing the count of the non-blank cells in the ranges using the COUNTA function. Note how we make sure all cell references are absolute, not relative, by inserting the "$" in front of both the row and column references.The logic for the third question is a bit tricky. We are going to use the OR and AND functions along with the COUNTA function. Basically, with the OR function we are allowing either one of the scenarios to be true. Each of the conditions uses the AND function. Let's break it down. The formula again is:=OR(AND(COUNTA($J$10:$J$16)<=2,COUNTA($G$18)=0),AND(COUNTA($G$18)=1,COUNTA($J$10:$J$16)=0))The OR function checks whether any of the arguments are true. The syntax for the OR function is =OR(logical1, logical2, logical3,...). Therefore, what the formula is saying is:Either:AND(COUNTA($J$10:$J$16)<=2,COUNTA($G$18)=0)must be true,ORAND(COUNTA($G$18)=1,COUNTA($J$10:$J$16)=0)must be true.The AND function checks whether ALL the arguments are true. So, either you can check up to two accommodations($J$10:$J$16), but the Other box ($G$18) must be empty OR you can enter something in the Other box ($G$18), but the count for the list ($J$10:$J$16) must equal zero.Download the file noted above and try various combinations to see how these work.Thanks for looking at this post and Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

Trackbacks/Pingbacks

  1. Three Flavors Of Drop Down Lists In #Excel | Excel Bytes - April 1, 2014

    […] 2 – Dates & Values Photos Tied To Drop Down Lists Data Validation Circle Invalid Data Limiting Survey Choices With Data Validation Dynamic Data Validation Lists Hyperlinks Using Data Validation Data Validation With Limiting […]

Leave a Reply