Forum Discussion
Data Validation Not Matching
mathetes Hey there! Thanks for getting back to me so quickly. I'll upload a sample copy of our spreadsheet. The names in here are not real ones.
If you scroll over to the right you will be able to see where it reads BFZ Status in pink. That specific column is where I am having the issue. In the top drop-down menu, you will see options such as 'Aged into Inactive Policy' through to 'Returned from Housed'. This menu does not seem to match what is showing in the drop-down in the cells. I'm specifically looking for 'Moved to Inactive'.
Thank you and let me know if this makes sense or you require any more information.
This is an impressive spreadsheet, and it is evidence of wonderful work in the community. It's a privilege to be able to help even in this little way.
The problem (I believe) is that the Data Validation was set up by entering the actual list of acceptable answers into the DataValidation dialog box. It looked like this when you opened it up:
That's a perfectly valid way to do Data Validation, but it is harder to edit, harder to maintain, harder to extend....all sorts of "harder's"!
I still use a list, but I've found over time that a much more flexible way to have a list for data validation purposes is to create a separate sheet altogether--where other "reference tables" can be posted as well, other data validation tables perhaps (although I am surprised to see that you don't appear to have DataValidation rules set for any of the other columns). That other way to use a list is to create the list at a place in the workbook where it can sit undisturbed and out-of-the-way (usually a distinct sheet solely for that purpose. And then just type the list in a column, as you'll see in this revised version of what you sent me.
The new tab at the very left contains that table (you can move it if you wish). Then we now are able to have a DataValidation dialog box that looks like this:
What this enables is a number of things:
- let's assume you want to change one that you never use, or just clarify the wording: all you need to do is change the table...no need to open the data validation dialog and re-apply it to all the affected cells
- let's assume you want to remove one of the choices: just remove it. If you delete the row, the reference in that dialog box will automatically adjust (however, be careful with that method if you've created other reference tables off to the side in those same rows)
- let's assume you want to ADD a choice: just add it, and then, yes, you'll need to go into the DataValidation dialog and extend range of cells referred to; after that, with one additional row, it will read =RefTables!$A$1:$A$8
There is another way to do the reference--using Excel's Named Range capability. That actually makes it even easier...but I'll let you research that on your own. If you want, come back and ask about it.
Thank youmathetes! I am going to try to implement this into our current list. Do you mind if I come back to ask a few questions? We have flags built into the system which may make things a little more complicated. For example, when someone is marked as Inactive the color changes beside their name.
Thank you again.
- The Conditional Formatting shouldn't be affected, since I left the wording unchanged (or at least I tried to). But by all means, feel free to come back with more questions.
Hi theremathetes,
I am writing in regard to our list again. Since I have made these changes to the data validation there is now some difficulty when I try to change the status of an individual to Inactive. I'll attach a sample without names here. As you can see, in the past if someone was moved to 'Aged into Inactive' they would be marked as such in column B. Is this something you could provide some insight into?
Thanks.