Today, a viewer from YouTube asks how to make sure that a price entered is less than the upper limit in a lookup table. Mike and Bill compare two different ways, one using Data Validation and one using Conditional Formatting. Learn Excel from MrExcel
Transcript of the video:
Hey, welcome back. It's another Dueling Excel podcast. I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from Excel is fun.
This is episode 82: allow below limit. Hey, Mike Girvin, welcome back. We received this question from aprima at YouTube and this one-- this one threw me for a loop, I’ve got to tell you.
They have some product names here and you can see that you can choose from the list.
They have a data validation set up there.
Then, they want to make sure that the amount entered is less, less than this upper limit over here in this table. So, you know, I thought VLOOKUP conditional formatting, I think, would be relatively easy, but to do data validation?
This one-- this is tough.
I knew that there was a custom option, custom, but then how do you write a formula that looks to see if the value in B2 is less than a lookup based on A2. So, we try this: equal B2 less than or equal to VLOOKUP of A2, comma-- All right, now, I got to get the address for that table, so I'm just going to click there. I’ll have to press F4 at that point, comma, 2, comma, false.
Let's assume that this is going to work. The error alert is going to be custom. It’s going to say, price too high. Price entered it is above the limit. Please try again. We’re going to be cool if we could get the actual limit there in the message. I don't think there's any way we can do that. We click OK.
Now, we have to copy that validation down.
So, I Ctrl C and I’m going to use paste special. So Alt E S and paste validation, OK and let's try it here. So, for Bellen, it looks like 15 should work. Great. 99 will not. Great, that's working. Cancel. Let's try something else.
We can look Aspen. So, see that 15 is already there. That's one place where validation falls apart. We'll try 11; that works. We’ll try 13 and it does not work. Okay, so, it's getting it. I initially thought that I can embed this VLOOKUP table right in the validation, but when I tried that it said, no, you're not allowed to use a rate constants and validation.
What's up with that? That’s crazy. All right, Mike, let's see what you have.
Mike: Thanks MrExcel. I love that data validation custom with the true/false logical formula using VLOOKUP. I think I'll actually steal exactly that formula, but I'll do it with conditional formatting. Another thing I like about your video, MrExcel, I love that copy and then paste special validation only.
All right, so I want to be able to put an Aspen here and then if I type 99 here, instead of the cell preventing me from putting it in, I want it to flash a color like red. If I put something less than the upper end from this lookup table here for Aspen, I want it to have no color.
All right, now I'm going to do conditional formatting. So, I'm going to highlight all of the cells and the active cell right there.
I'm actually going to build a true/false format.
In fact, why don't I just build it here in the cells and then copy it and paste it up there? Sometimes it's easier to see the true false formula in the cells before you do conditional formatting or for that matter, data validation also.
All right, so equals, hey, is anything in this cell-- and we want it to show true when it's above because that conditional formatting needs to be applied when it's above, which means a violation of the rule any time that is greater than VLOOKUP. The lookup value is going to be the product name, comma, here's the table, F4 to lock it, comma.
We’re looking up a value here, returning the value here. So, that's the second column of our lookup table and finally, true or false exact match. We are looking up words. So, I'm going to put a zero for false. All right, so then I'm going to copy this down and you can see what will happen now is if I put 99 here, this will be true. The falses and the errors will be ignored. Conditional formatting will not be applied.
Now, important you got to copy wherever you copy your formula. I’m going to copy mine from the top cell on the range. I'm going to copy it and then when I highlight the range, that first cell, because I took this first formula right here, has to be the active cell.
Then I go up to conditional formatting in the home ribbon, manage rules or Alt O D, new rule. I'm going to paste it right here, Ctrl V, and then do some formatting.
Let's see. Font; I'll do a light font and a dark value color for fill. Click OK. Click OK. So, there it is. Write data validation if you applied it and there was a value there and it didn't really give you a signal. All right, so, now if I type 150, it doesn't prevent me from putting it in, but it does give me a color when I go below. Boom, it works.
So now, I select this one here, Quad and type in 55. No way, but 22? No way, also. Quad is 19, so we put in 18 and then they are allowed.
Certainly, we can change this to, let's see 14. Let's change this to Aspen, right? So, immediately even though there was something in the cell, conditional formatting says, hey, that's red; you're not allowed. All right, I'll throw it back to MrExcel.
Bill: So, hey, Mike, that's cool. I like the conditional formatting, especially if they go back and change the value later. It still highlights in red. Your trick with entering the formula in the cell first, you know, I got burned by that on the data validation because I originally set up a VLOOKUP and I put an array constant in there. I used your trick: select the range, press F9.
Of course, that worked in the cell, but when I went to the data validation, it said can't do that. Can’t have an array constant in data validation, which if you think about it is really kind of silly. But anyway, that’s the way it goes.
All right, well, hey, I want to thank everyone for stopping by. We’ll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
This is episode 82: allow below limit. Hey, Mike Girvin, welcome back. We received this question from aprima at YouTube and this one-- this one threw me for a loop, I’ve got to tell you.
They have some product names here and you can see that you can choose from the list.
They have a data validation set up there.
Then, they want to make sure that the amount entered is less, less than this upper limit over here in this table. So, you know, I thought VLOOKUP conditional formatting, I think, would be relatively easy, but to do data validation?
This one-- this is tough.
I knew that there was a custom option, custom, but then how do you write a formula that looks to see if the value in B2 is less than a lookup based on A2. So, we try this: equal B2 less than or equal to VLOOKUP of A2, comma-- All right, now, I got to get the address for that table, so I'm just going to click there. I’ll have to press F4 at that point, comma, 2, comma, false.
Let's assume that this is going to work. The error alert is going to be custom. It’s going to say, price too high. Price entered it is above the limit. Please try again. We’re going to be cool if we could get the actual limit there in the message. I don't think there's any way we can do that. We click OK.
Now, we have to copy that validation down.
So, I Ctrl C and I’m going to use paste special. So Alt E S and paste validation, OK and let's try it here. So, for Bellen, it looks like 15 should work. Great. 99 will not. Great, that's working. Cancel. Let's try something else.
We can look Aspen. So, see that 15 is already there. That's one place where validation falls apart. We'll try 11; that works. We’ll try 13 and it does not work. Okay, so, it's getting it. I initially thought that I can embed this VLOOKUP table right in the validation, but when I tried that it said, no, you're not allowed to use a rate constants and validation.
What's up with that? That’s crazy. All right, Mike, let's see what you have.
Mike: Thanks MrExcel. I love that data validation custom with the true/false logical formula using VLOOKUP. I think I'll actually steal exactly that formula, but I'll do it with conditional formatting. Another thing I like about your video, MrExcel, I love that copy and then paste special validation only.
All right, so I want to be able to put an Aspen here and then if I type 99 here, instead of the cell preventing me from putting it in, I want it to flash a color like red. If I put something less than the upper end from this lookup table here for Aspen, I want it to have no color.
All right, now I'm going to do conditional formatting. So, I'm going to highlight all of the cells and the active cell right there.
I'm actually going to build a true/false format.
In fact, why don't I just build it here in the cells and then copy it and paste it up there? Sometimes it's easier to see the true false formula in the cells before you do conditional formatting or for that matter, data validation also.
All right, so equals, hey, is anything in this cell-- and we want it to show true when it's above because that conditional formatting needs to be applied when it's above, which means a violation of the rule any time that is greater than VLOOKUP. The lookup value is going to be the product name, comma, here's the table, F4 to lock it, comma.
We’re looking up a value here, returning the value here. So, that's the second column of our lookup table and finally, true or false exact match. We are looking up words. So, I'm going to put a zero for false. All right, so then I'm going to copy this down and you can see what will happen now is if I put 99 here, this will be true. The falses and the errors will be ignored. Conditional formatting will not be applied.
Now, important you got to copy wherever you copy your formula. I’m going to copy mine from the top cell on the range. I'm going to copy it and then when I highlight the range, that first cell, because I took this first formula right here, has to be the active cell.
Then I go up to conditional formatting in the home ribbon, manage rules or Alt O D, new rule. I'm going to paste it right here, Ctrl V, and then do some formatting.
Let's see. Font; I'll do a light font and a dark value color for fill. Click OK. Click OK. So, there it is. Write data validation if you applied it and there was a value there and it didn't really give you a signal. All right, so, now if I type 150, it doesn't prevent me from putting it in, but it does give me a color when I go below. Boom, it works.
So now, I select this one here, Quad and type in 55. No way, but 22? No way, also. Quad is 19, so we put in 18 and then they are allowed.
Certainly, we can change this to, let's see 14. Let's change this to Aspen, right? So, immediately even though there was something in the cell, conditional formatting says, hey, that's red; you're not allowed. All right, I'll throw it back to MrExcel.
Bill: So, hey, Mike, that's cool. I like the conditional formatting, especially if they go back and change the value later. It still highlights in red. Your trick with entering the formula in the cell first, you know, I got burned by that on the data validation because I originally set up a VLOOKUP and I put an array constant in there. I used your trick: select the range, press F9.
Of course, that worked in the cell, but when I went to the data validation, it said can't do that. Can’t have an array constant in data validation, which if you think about it is really kind of silly. But anyway, that’s the way it goes.
All right, well, hey, I want to thank everyone for stopping by. We’ll see you next week for another Dueling Excel podcast from MrExcel and Excel is Fun.
