Jon wants to set up validation to see if the result of a calculation exceeds a certain value. In Episode 892, we take a look at using Data Validation with a formula.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, this Friday is the 10th anniversary of MrExcel, so if you happen to be watching this when the podcast comes out, I will let you know about our webinar-- doing a free webinar November 21st, 2008.
It's going to start at 11am Eastern time in the U.S., and run for a hundred minutes.
If you want to sign up, it's free; just come to MrExcel.com/webinar.html.
There's still plenty of space there for people to sign up; there's some cool tips and tricks along the way.
Well, today's Excel question is sent in by John.
John is setting up a spreadsheet, and he has programs-- sporting programs-- that run for a certain number of weeks-- you know, something like, at the Y, you know-- and it has a certain start date and he wants to set up validation to make sure that those two values do not extend beyond a certain date-- in this case, March 31st.
So basically, he was trying to set up validation over in Column C, where he has the formula that says we're going to take the date in B2 and add 7 times A2, to figure out when that end date is.
And he set a validation in Column C, but you can't set up validation in Column C because we're not entering any data in Column C. The validation will never fire.
But with some clever use of validation, we can come back here to Column B and probably figure out a way to solve this.
So we're going to go into Data, Validation, and instead of allowing any value or most of the things that we allow, I'm going to allow something called Custom.
Now, with Custom, basically, we can write any formula that's going to evaluate to true or false.
And so I'm going to say =MONTH(B2+A2*7).
Now that's going to calculate a date, and I want to see the month.
Now, he wants to make sure that it's less than March 31st.
I'm going to say that it has to be less than 4, because the MONTH function is going to give me "1" for January, "2" for February, and so on.
Now, we can customize this a little bit back on the Error Alert tab, and say, you know, Title: "Ends Too Late"; Error message: "This Start Date and # of Weeks will End after March 31st.
Please Try Again"; click OK.
Now, once we have that validation, I'm going to copy that cell; I'm going to paste it down to the whole range; I'll use Edit, Paste Special, and then choose Validation-- that will copy just the validation down; click OK.
And basically, now, if we come here, so here's a program that lasts for 10 weeks.
If we would start it on 3/1/2008, that would calculate something that's too late and so the validation pops up and we can click Cancel or Retry-- Cancel will go back to the original value.
Now it's possible that someone would change the number of weeks, so here on the sheet I have actually already set up a couple of different validations back in Column A-- used a slightly different formula, you know, that basically checks to see if the month of A2*7.
So if you change either A or B and it causes the value to go beyond March 31st-- so here I'll do something for 21 weeks-- the validation will pop up and prevent them from doing that.
So rather than attacking the validation in Column C where the formula is, we go back and write slightly different validations in A and B to solve the problems.
I want to thank John for sending that question, I want to thank you for stopping by.
Hopefully, we'll see you this Friday for the MrExcel 10th Anniversary Webinar.
See you next time for another netcast from MrExcel.
Well, this Friday is the 10th anniversary of MrExcel, so if you happen to be watching this when the podcast comes out, I will let you know about our webinar-- doing a free webinar November 21st, 2008.
It's going to start at 11am Eastern time in the U.S., and run for a hundred minutes.
If you want to sign up, it's free; just come to MrExcel.com/webinar.html.
There's still plenty of space there for people to sign up; there's some cool tips and tricks along the way.
Well, today's Excel question is sent in by John.
John is setting up a spreadsheet, and he has programs-- sporting programs-- that run for a certain number of weeks-- you know, something like, at the Y, you know-- and it has a certain start date and he wants to set up validation to make sure that those two values do not extend beyond a certain date-- in this case, March 31st.
So basically, he was trying to set up validation over in Column C, where he has the formula that says we're going to take the date in B2 and add 7 times A2, to figure out when that end date is.
And he set a validation in Column C, but you can't set up validation in Column C because we're not entering any data in Column C. The validation will never fire.
But with some clever use of validation, we can come back here to Column B and probably figure out a way to solve this.
So we're going to go into Data, Validation, and instead of allowing any value or most of the things that we allow, I'm going to allow something called Custom.
Now, with Custom, basically, we can write any formula that's going to evaluate to true or false.
And so I'm going to say =MONTH(B2+A2*7).
Now that's going to calculate a date, and I want to see the month.
Now, he wants to make sure that it's less than March 31st.
I'm going to say that it has to be less than 4, because the MONTH function is going to give me "1" for January, "2" for February, and so on.
Now, we can customize this a little bit back on the Error Alert tab, and say, you know, Title: "Ends Too Late"; Error message: "This Start Date and # of Weeks will End after March 31st.
Please Try Again"; click OK.
Now, once we have that validation, I'm going to copy that cell; I'm going to paste it down to the whole range; I'll use Edit, Paste Special, and then choose Validation-- that will copy just the validation down; click OK.
And basically, now, if we come here, so here's a program that lasts for 10 weeks.
If we would start it on 3/1/2008, that would calculate something that's too late and so the validation pops up and we can click Cancel or Retry-- Cancel will go back to the original value.
Now it's possible that someone would change the number of weeks, so here on the sheet I have actually already set up a couple of different validations back in Column A-- used a slightly different formula, you know, that basically checks to see if the month of A2*7.
So if you change either A or B and it causes the value to go beyond March 31st-- so here I'll do something for 21 weeks-- the validation will pop up and prevent them from doing that.
So rather than attacking the validation in Column C where the formula is, we go back and write slightly different validations in A and B to solve the problems.
I want to thank John for sending that question, I want to thank you for stopping by.
Hopefully, we'll see you this Friday for the MrExcel 10th Anniversary Webinar.
See you next time for another netcast from MrExcel.
