Sometimes you want to have cell validation, but you need a way for the person using the spreadsheet to override the validation list, especially in the case where there is a brand new value that you have not yet added to the list. Episode 381 shows you how to have the Validation dropdown ease up.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
All this week we've been talking about using Data Validation to set up a drop-down list.
Now the one problem with setting up a drop-down list is that, when you send this out to the sales reps, they might say “Well wait a second, there's a brand new product or a brand new district that's not in your list!” I hate to give the salesforce any excuse for not sending the forecast back.
And so we might want to configure validation to kind of ease up a little bit, give the sales reps a chance to change their value to override the list.
If you want to do that, you go to Data, Validation, and go to the Error Alert tab.
There's three different styles, and the default style is the Stop style, that's where if they enter any value that's not valid, it's not going to let them enter it.
But if you change that to say Warning, then you can actually tell them that they should select from the list unless the district is brand-new.
If the district is brand-new, then they can go ahead and confirm that this is new.
Now, when we click OK here, and we enter something different, you see that there's three buttons, they have a choice to continue: Yes, No, or Cancel, and no is the default button.
So they actually have to go through and click Yes, instead of just hitting Enter.
The other option in data validation is to choose the Information.
Now this is the loosest one, this is where it just says “Hey, you selected something from this that’s not from the list, and the default choice is OK.” So if they enter something here, basically all I have to do is hit Enter, and they're on their way.
So this last choice, the Information choice, is really just a good choice to kind of alert them that they entered something wrong, and then you're hoping that they will go back and correct it.
My sales reps would never do that, of course, I would probably choose the one in the middle, so that way they could at least override the list if they had a good reason to.
That way I'm not preventing them from sending their forecasts in, but you know, it kind of dogs them and makes them choose from the list unless they have a good reason.
Hey, there you have it, another netcast on how to control the Data Validation, we'll see you next time or another netcast from MrExcel!
All this week we've been talking about using Data Validation to set up a drop-down list.
Now the one problem with setting up a drop-down list is that, when you send this out to the sales reps, they might say “Well wait a second, there's a brand new product or a brand new district that's not in your list!” I hate to give the salesforce any excuse for not sending the forecast back.
And so we might want to configure validation to kind of ease up a little bit, give the sales reps a chance to change their value to override the list.
If you want to do that, you go to Data, Validation, and go to the Error Alert tab.
There's three different styles, and the default style is the Stop style, that's where if they enter any value that's not valid, it's not going to let them enter it.
But if you change that to say Warning, then you can actually tell them that they should select from the list unless the district is brand-new.
If the district is brand-new, then they can go ahead and confirm that this is new.
Now, when we click OK here, and we enter something different, you see that there's three buttons, they have a choice to continue: Yes, No, or Cancel, and no is the default button.
So they actually have to go through and click Yes, instead of just hitting Enter.
The other option in data validation is to choose the Information.
Now this is the loosest one, this is where it just says “Hey, you selected something from this that’s not from the list, and the default choice is OK.” So if they enter something here, basically all I have to do is hit Enter, and they're on their way.
So this last choice, the Information choice, is really just a good choice to kind of alert them that they entered something wrong, and then you're hoping that they will go back and correct it.
My sales reps would never do that, of course, I would probably choose the one in the middle, so that way they could at least override the list if they had a good reason to.
That way I'm not preventing them from sending their forecasts in, but you know, it kind of dogs them and makes them choose from the list unless they have a good reason.
Hey, there you have it, another netcast on how to control the Data Validation, we'll see you next time or another netcast from MrExcel!
