A few times a year I email workbooks containing surveys to people at about 80 schools. The overall process goes something like: I get a list of a couple of thousand records, which is then split into multiple per-school survey workbooks, which are then emailed to the schools. School staff complete the surveys and email them back. They are then merged back into one file and analyzed. Until we figure out some kind of web-based SharePoint-type system, this all works pretty well. I’ve mostly automated the emailing – adjusting subject lines, recipient lists, attachments and body on a per-school basis – and the splitting and re-merging is all push-button, so it’s a pretty efficient process.
The workbook/surveys are designed to be as user-friendly as possible, both for the people completing them and for the those analyzing the results. I use a combination of data validation and conditional formatting to guide the recipients. Ideally this might also include some VBA for things the data validation can’t handle, but it’s not worth confusion and maintenance issues that would result. So instead the workbooks contain additional conditional formatting that warns people when their data entry has gone astray. It also uses a concept I think of as conditional named ranges to provide appropriate data validation choices.
In the above picture, I’ve imagined some kind of International Pie Lovers Association, with a survey for their annual dinner. The meal choices are simple (Yes/No and Veggie/Meat) but they can choose up to three slices of pie, with a separate data validation dropdown for each slice. Yellow cells indicate where choices need to be entered. Orange indicates an error: cells that were entered when a condition called for it, but where that condition is no longer true, for example, under pie slices “3” was entered originally and “Banana Cream” was chosen, but then the number of slices was reduced to “2.”
In order to make data entry more user-friendly, I came up with data validation which points at a named range that resolves to one or more cells if a condition is true, and to nothing if it’s not. In the picture below, the condition is true in the first cell in the “Pie 1” column and so a list of pies is available, but if you clicked the dropdown in the “Pie 2” column there would be no choices. That’s because the user chose 1 in the “Pie Slices” column, so the conditional range equates to nothing for the “Pie 2” column.
There are many ways to do condtional data validation and Debra Dalgleish has lots of great info on her website. In the dark days before I had my own blog she was kind enough to post about this particular conditional range concept.
To set up the conditional range, I first create a named range called “rngPies” that points at a static list of pies in column M. Then I create the conditional range, called “rngValPie,” which points at rngPies if the condition is met, and points at nothing if it’s not. The formula for rngValPie (with I2 selected) is:
In English it says “If the number of slices selected is less than or equal to the number in this column, use rngPies, otherwise use nothing.” Here it is in the indispensable Name Manager.
The data validation then points at rngValPie. If a pie should be chosen the data validation shows the list, otherwise there’s no choices available.
Note that when you enter a conditional range in the Data Validation dialog, the condition needs to be true, otherwise you’ll get an error message. For instance, if I try to enter the data validation while J2 is the active cell, I’ll see this:
Turning the cells orange if unneeded data is entered is accomplished with conditional formatting and some helper columns to the left of the data entry area.
The helper columns contain formulas that feed the conditional formatting. I could put the formulas from the helper columns directly in the conditional formatting, but do it this way because it’s easier for us back at the office to spot invalid data by filtering the helper columns.
This system works well for us and for the folks at the schools (at least as far as I can tell). The amount of user-helpfulness is in good balance with the ease of maintenance. If you’d like to look at a sample workbook that’s Excel 2003-2010 compatible, here’s the zipped file.
Note: for another use of conditional ranges, which has worked very well for me, see this Jan Karel Pieterse post on Daily Dose of Excel.