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

[JakeExcel]

Hello,

I have figured out how to use the data validation feature to create dropdown options. This is useful to me because I am working on a project that involves me sending somebody a spreadsheet full of different types of errors that need to be fixed. They can use the dropdown menu to tell me either:

1. I fixed this!
2. I’m not able to fix this

The following screenshot demonstrates:

But here’s my problem. Let’s say that they can’t fix the error, so they select “I’m not able to fix this error”, then they send the spreadsheet back to me. I open the spreadsheet, and see the following:

In this instance, I am not able to tell that the error they are unable to fix is the “Birthday Error”. All I know is that there was an error that they weren’t able to successfully fix. Is there any way I can alter the data validation setup so that the three drop-down options that show up are:

1. I fixed this!
2. I’m not able to fix this
3. “Birthday Error” (or rather, whatever value was originally typed into that cell. So if instead it was a health insurance error it would say “Health Insurance Error”)

Thanks!

[#2]

I'm not sure I'm fully grasping the layout of your sheet and the nature of the difficulty. You seem to be saying that "Birthday Error" is in the cell now, the SAME cell where the other person can enter "I fixed" or "Not able" to fix.
IF my reading of your situation is accurate, I wonder, first, why you couldn't put the drop down validation choices into an adjacent cell (or a cell at the end of a row). So that if the answer is "Not able to fix" you still have the original "Birthday Error" (or whatever) showing. So: adjacent cell would be the first suggestion.
Another possibility, assuming there's a finite number of errors (you name two) would be simply to add a few valid answers, along the following lines:
Fixed
Error Remains-Birthday
Error Remains-Health Ins
Error Remains-Misc1
Error Remains-Misc 2
etc.
Now, if what you are asking is simply "Is there any way I can alter the data validation setup," the answer is yes. If you know where that validation table is, you can add other answers. You'd then have to increase the size of the range used for validation in the cells in question.. Write back if that's your issue.

[#3]

Thanks for your reply @mathetes ! Your reading of my situation is accurate in that yes, "Birthday Error" is in the same cell where the other person can enter "I fixed" or "not able"

I prefer not to put the drop down validation choices into an adjacent cell because I think it would make my spreadsheet too overwhelming for the other person as there's lots of other columns being used for other purposes here. There are also not really a finite number of errors, as new types of errors emerge from time to time.

I appreciate that you've suggested alternative solutions, however what I am hoping to do is alter the data validation setup so that whatever is already typed into the cell will show up as a dropdown option in that same cell.

Currently I have it set up as such so that it's referencing my list which contains "I fixed" and "Not able to fix":

But maybe if I use the custom feature in the screenshot below, there will be some way I can write a formula so that it references whatever is already in the cell that is to contain the dropdown menu?

Thanks!

[#4]

@JakeExcel 

I agree with the assessment given by @mathetes to use an adjacent cell.  If you really need this to work in 1 cell the only way I can think of at the moment is to keep your validation list in the cell and pair it with some conditional formatting.  The conditional formatting would check the value in the cell.  For example, if "No" is picked then custom format: 0;0;0;"Birthday Error"

The issue with this is the custom format is all for show. It does not place that text in the cell.

[#5]

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.