Using Data Validation to make drop-downs that reference other cells

Not knowing what you're dealing with here to begin with makes this difficult. I will say that you are using Data Validation in a way that is markedly different from what it's generally used for, which is (as the term implies) to validate an entry, i.e., to make sure it's one of the only acceptable entries from among a finite list. You're use isn't wrong per se, but it is unusual, in that you're using it for feedback (or "comment") as to whether a task was successfully completed, and if not you're seeking more info on "Why not?"

Now, I want to emphasize that the usual application of "Data Validation" would be precisely this: to prevent the kinds of errors that you're seeking to fix. If there are, as you say, lots of other columns being used for other purposes, I'm beginning to wonder whether you could do a massive clean-up and then modify in a major way what the basic design is.

Can I ask that: What is the whole spreadsheet about? Are you using Excel to do mathematical modeling (or financial record keeping) or anything that actually uses the calculation capabilities of Excel? If not that, are you using it--and this is legitimate--as a database, to store info on people who are members of an organization? [The fact that you've got birthdate in there suggests possibly the latter; the fact that health insurance is in there too]

IF what you have is essentially a database, then, yes, you do need valid data, but it's far better to design it so that dates (for example) cannot be entered in an invalid fashion, rather than to jump through hoops to fix incorrectly entered data. And so on... you may not be able to prevent misspellings of names, but you can do things to reduce the likelihood of needing to do the kind of after-the-fact correction you seem to be engaged in.

So, if you don't mind sharing this kind of information--if it's not proprietary--could you either upload a sample of your actual spreadsheet, if not the whole thing, OR, at the very least, provide some sort of description of the bigger picture here, what you're storing and for what purpose. Maybe then some of us can give you some recommendations for preventive actions rather than just helping you put a bandaid on this one.