Mike writes in with a question that is way too hard for a Friday, and way too hard considering it is dawn in Maui. Mike has what sounds like a simple task a checkbook register in Excel, but needs to create a tricky validation that will allow either an integer check number or one of a handful of values such as ATM, EFT, etc. Episode 491 shows you how to set up data validation to handle this problem.
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 MrExcel netcast. I'm bill Jelen.
Well as you can see I'm on vacation in Maui this week and I'm trying to get some podcasts done before everyone wakes up here.
And I have a question from Mike. I realize this podcast is probably going to show on a Friday and this is probably way too hard to try and do in a single podcast.
But let's give it a shot.
Mike's trying to set up a checkbook in excel.
He's using fairly sophisticated tools.
He's using data validation for his list of categories.
He has a list of all the available categories and set up data validation from a list.
For his list of payees, he set up a list of all the previous payees but turned off the error alerts that way if he has a brand new payee, he starts shopping in a new grocery store for example, validation doesn't give him a problem.
But in his check number field, Mike wants to set up validation that says.
Either I'm entering a check number which means that we have to use an integer or It's one of the values that I've used previously for example, DD for direct deposit, or EFT for electronic funds transfer, ATM for an ATM withdrawal.
And he wants to know how we can set up validation to do that and do a list drop-down as well.
Now I can't do the list drop-down, but let's walk through how I can do everything else .
In data validation, most of the time I talk about setting up a custom list.
This is one of my standard things I do in the power excel seminar but today, we're going to use something called custom and in custom we have to write a formula that evaluates to true or false.
If the answer is true excel will allow the value to come in.
If it's false, excel will say hey, that value is not valid.
So we have to write a really complicated formula that's going to handle either situation.
The first thing we have to do is see if the value that's entered as a number, we need to see if it's an integer.
And the best way to check to see that is to look and see if the integer of the number INT function is the same as the number.
So here I have a simple formula set up that says if B1=INT(B1) and you see here, we have 1 and a 2, those are true, but at 1.2 or 2.1 those are false, because the integer of 2.1 is 2 which isn't equal to 2.1.
And that's a good way to tell whether or not you have an integer.
I'm going to go on to the next worksheet, and here I've entered a few different values EFT, DD, ATM and my formula here, let's take a look at it down in row 6.
My formula says we want to use the COUNTIF function and basically COUNTIF everywhere from B1, and that's an absolute reference down to B5, that's the row right before the current row is equal to the value B6.
And we're going to count how many times that happens.
If It's a brand new entry, the count will be zero.
So basically I want to say, I want to see if the COUNTIF >0 and you'll see how this works.
So here I have EFT when I got to the second EFT the value was true.
Second DD the value's true.
ATM the value's false but then if I put in ATM again, the value's true.
So hopefully in Mikes list he has a list of all the valid values.
So now we have to try and put those two things together.
I learned the first time I try to set this up that if I did the integer trick. If the integer of B2 is equal to B2 and we have a value like DD that gives me an error and so I came here to my third worksheet and I go into data validation, I set up an IF formula. if I have a number in B6 then I check to see if B6 is equal to the integer B6.
Otherwise, I do the COUNTIF function counted from $B$1$ down to B5 and I have to note that I'm doing this in cell B6.
I have to write this formula for the active cell, if it's equal to B6>0.
That basically sets up my formula. I can click OK now.
and what I did was I deleted the value in that cell. Copy the cell and pasted it down to however far I think I might be entering check numbers. So that way it copies the formula correctly.
Now what should happen is if I enter a check number like 1 2 3 4 It's perfect.
If I enter a check number with a decimal 1234.1 I get an error.
If I enter a value that's already been entered before. ATM. No problem.
If I enter a value, that's new, it will say you can't do that.
Now Mike wanted to drop down in the cell, and I don't have a good way to do that, other than to go to "Tools" "Options" and on the "Edit" tab we look at the bottom check box and "Enable Auto Complete for cell values" and that basically will allow me to type the A and hit enter or the D or the E.
This works perfectly because we have only a few values.
If we happen to have a really long value in B1 and perhaps a really longer value in B2, that really defeats the whole AutoComplete.
When I start typing, it doesn't know whether I want really long or really longer and so until I get to the space, it doesn't fill things in, but really in Mike's situation here where we just have ATM EFT, DD. I'm going to say that using AutoComplete will solve the problem.
So there you have it Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Well as you can see I'm on vacation in Maui this week and I'm trying to get some podcasts done before everyone wakes up here.
And I have a question from Mike. I realize this podcast is probably going to show on a Friday and this is probably way too hard to try and do in a single podcast.
But let's give it a shot.
Mike's trying to set up a checkbook in excel.
He's using fairly sophisticated tools.
He's using data validation for his list of categories.
He has a list of all the available categories and set up data validation from a list.
For his list of payees, he set up a list of all the previous payees but turned off the error alerts that way if he has a brand new payee, he starts shopping in a new grocery store for example, validation doesn't give him a problem.
But in his check number field, Mike wants to set up validation that says.
Either I'm entering a check number which means that we have to use an integer or It's one of the values that I've used previously for example, DD for direct deposit, or EFT for electronic funds transfer, ATM for an ATM withdrawal.
And he wants to know how we can set up validation to do that and do a list drop-down as well.
Now I can't do the list drop-down, but let's walk through how I can do everything else .
In data validation, most of the time I talk about setting up a custom list.
This is one of my standard things I do in the power excel seminar but today, we're going to use something called custom and in custom we have to write a formula that evaluates to true or false.
If the answer is true excel will allow the value to come in.
If it's false, excel will say hey, that value is not valid.
So we have to write a really complicated formula that's going to handle either situation.
The first thing we have to do is see if the value that's entered as a number, we need to see if it's an integer.
And the best way to check to see that is to look and see if the integer of the number INT function is the same as the number.
So here I have a simple formula set up that says if B1=INT(B1) and you see here, we have 1 and a 2, those are true, but at 1.2 or 2.1 those are false, because the integer of 2.1 is 2 which isn't equal to 2.1.
And that's a good way to tell whether or not you have an integer.
I'm going to go on to the next worksheet, and here I've entered a few different values EFT, DD, ATM and my formula here, let's take a look at it down in row 6.
My formula says we want to use the COUNTIF function and basically COUNTIF everywhere from B1, and that's an absolute reference down to B5, that's the row right before the current row is equal to the value B6.
And we're going to count how many times that happens.
If It's a brand new entry, the count will be zero.
So basically I want to say, I want to see if the COUNTIF >0 and you'll see how this works.
So here I have EFT when I got to the second EFT the value was true.
Second DD the value's true.
ATM the value's false but then if I put in ATM again, the value's true.
So hopefully in Mikes list he has a list of all the valid values.
So now we have to try and put those two things together.
I learned the first time I try to set this up that if I did the integer trick. If the integer of B2 is equal to B2 and we have a value like DD that gives me an error and so I came here to my third worksheet and I go into data validation, I set up an IF formula. if I have a number in B6 then I check to see if B6 is equal to the integer B6.
Otherwise, I do the COUNTIF function counted from $B$1$ down to B5 and I have to note that I'm doing this in cell B6.
I have to write this formula for the active cell, if it's equal to B6>0.
That basically sets up my formula. I can click OK now.
and what I did was I deleted the value in that cell. Copy the cell and pasted it down to however far I think I might be entering check numbers. So that way it copies the formula correctly.
Now what should happen is if I enter a check number like 1 2 3 4 It's perfect.
If I enter a check number with a decimal 1234.1 I get an error.
If I enter a value that's already been entered before. ATM. No problem.
If I enter a value, that's new, it will say you can't do that.
Now Mike wanted to drop down in the cell, and I don't have a good way to do that, other than to go to "Tools" "Options" and on the "Edit" tab we look at the bottom check box and "Enable Auto Complete for cell values" and that basically will allow me to type the A and hit enter or the D or the E.
This works perfectly because we have only a few values.
If we happen to have a really long value in B1 and perhaps a really longer value in B2, that really defeats the whole AutoComplete.
When I start typing, it doesn't know whether I want really long or really longer and so until I get to the space, it doesn't fill things in, but really in Mike's situation here where we just have ATM EFT, DD. I'm going to say that using AutoComplete will solve the problem.
So there you have it Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
