How do I supercharge Excel's Data Validation tool?

June 3, 2015 7:50 AM

(Background at the top; question at the bottom) I work for a tech company and clients routinely send us their data for us to process. These are quasi-employee records, including the employee's department, location, age, etc. They give us all of the valid selections for each of these fields as part of their initial account setup and we can change them at any time. These are the selections they've chosen and most often match what is in their HRIS. But, they often send us mismatching data...

If the data they send us (via .CSV file) does not match (let's say they send over the employee's department as 'HR' and we have it setup in their account as 'Human Resources'), it's a mismatch and that record won't be processed. They get an error report and have to resubmit the corrected data with the next day's file.

Telling them that mismatches result in errors sometimes helps, but they don't always seem to get it. So I thought providing them with a template using the data validation feature in Excel would help reduce errors. I can go behind the scenes for each column and limit it to just a valid selection for each field. This works beautifully if the client is manually keying in this information - it provides them with a drop down box for each field and any selections not on the list gives an error message.

The tricky part is, we have some clients who pull this data from their HRIS. Copying and pasting data into the data validation worksheet overwrites the rules - any value is accepted.

So, my question is, are there settings in Excel so I can require only valid selections while still allowing them to copy/paste their data from another spreadsheet? And if I can, how are errors handled? If they have a 30 row submission and there are errors in two of the fields, does the whole thing not get copied or can an error message pop up with the relevant cells that are wrong?

posted by Twicketface to Technology (3 answers total)

Not the most elegant solution but one that would be fast would be to build a macro that they would run after having downloaded the data. The macro would:
1. Cut-and-paste the selection into a new sheet/work area
2. Use the OR function that would test the "Department" cell vs the approved list of field names (so A4="HR" OR A4= "Payroll", etc); you would list all acceptable responses in the OR statement. If the response came back TRUE, you'd know that the cell in question is an acceptable answer and if the response came back FALSE you'd know that there was an error.
3. you could use conditional formatting to identify the error, or the macro could sort the data by the TRUE/FALSE field and there would be a list at top of any exceptions.

I'm assuming that they are downloading the data in the same format/number of columns each time. If not, you could do an hlookup or similar to sort out the fields you are interested in testing.
posted by limagringo at 8:13 AM on June 3, 2015

Macros a are a good start for this sort of stuff. Macro's are really just VBA in the background and you can edit them at a very fine level by pushing Alt-F11. You can also write completely custom VBA to do almost anything you want.

I've been doing a little bit of VBA programming lately and I've found this book to be a great help.
posted by Confess, Fletch at 8:21 AM on June 3, 2015 [1 favorite]

Best answer: Your template with data validation already applied idea would work if they copied & pasted the information in using the Paste Values option. (Copy as normal, to paste right click & under the Paste Options section in the right menu menu, select the button with '123'). Incorrect values can then be seen by turning on the Data Validation | Circle Invalid Data option. You could record turning on the Circle Invalid Data as a little macro & then attach it to a button shape on the template spreadsheet, labelled "Click to check for errors".

These 2 steps would be very simple & quick for the people adding the data, but would depend on them caring enough to actually do it.
posted by cantthinkofagoodname at 3:36 AM on June 4, 2015 [1 favorite]