Lessons

Lesson Info

1:30 pm - Data Validation: Date

We're gonna do some other ones here, okay? You also saw what the error message was and we did that that was the generic error message maybe you want to keep that there? Maybe not. Okay, so now let's go ahead and dio date entered and again first I want to do is simply highlight everything and I'm gonna go back to date a validation and I'm gonna go here and this time so far we've got his whole number we've done list but I also understand you could do date you can do decimal right could do all these things here and it's all gonna be determined by what you are planning to do with the information and what kind of stuff you want to make sure stays in there text like might be something also you want people to put notes in there but not too long or whatever it is right you can see here we have minimum and maximum between all that kind of stuff but let's go over here to date and you see here data's understand this date it's like okay, you only want stuff that's a start date between this start d...

ate and between this and date. All right, so it's going to be pretty straightforward now so I want to make a shirt on ly in two thousand thirteen so very simply I was going to do one life in one hyphen twenty thirteen and then you say twelve hyphen thirty one thirteen all right okay and now I'm here I'm going to see it's essentially going to be very similar to this I'm just gonna put my information so let's put in three three thirteen great it took it that's fantastic I'll do another one for three thirteen great we're on a roll okay and then I say two three fourteen okay, I get another message okay? Because it's seeing that this is not within that valley criteria that I specified okay so well see that how we could do it not only for numbers whole numbers of a range but we could also do it for dates within a range and you could just really extrapolate that information and put it towards other things as well where you're working with text length and decimals and all that kind of stuff too and you can you can certainly apply those things all right so let's go in and get out of that and then let's just try text length right now so let's go ahead into say notes all right now uh when we're dealing with oops I still have something left over and hear something just get rid of those and we'll go back to hear and to say clear all okay, so I have that they're so one thing I like tio again remind you of when you're working with like notes and stuff is we did a little bit of yesterday of working with the rap texts you recall that okay, so we have the ability to wrap the text when something gets too long notes tend to be a little bit more of a narrative right could be a couple of sentences could be whatever right? So we want to be ableto have notes in there but we want to limit it to let's just say, you know, uh, twenty five characters just for the sake of this exercise okay, so I'm going to go to make this a little bit wider, but I'm also going to do my rap text this is also just a little review what we did yesterday click on rap tax and again, nothing appears to happen because it's sort of baked in into the back of the cell now with this highlighted I'm going to choose data women, shoes, data validation this time I'm going to say text length all right, so it should be between and we also want people going put something in there should be at least let's just say two characters and a maximum and let you say twenty five okay and now I have that in there all right? Okay, I'm gonna say this client was very nice I comes in that's great okay, now this client lives very far and it is hard to get in touch with okay and see that that's a very long sentence meaning it's over twenty five so we want actually able to kind of gauge those things we want to be able to predict what's going to be good what's going to be bad so we can make sure that you know, we have whatever information we want tohave okay, so it's the same exact thing here so we come back to this again just just remember just just remember go away okay? You can see all the stuff is very similar so you really again just want to ask yourself the question what do you want to be validating? All right, so and also what's this going to help you make sure that you could do it quickly and also make sure that you don't get any sort of false positives or anything like that okay, so I'm gonna come over onto the mac side and do the same thing, but how are we doing with questions? You sure I follow so maybe this maybe you've already covered this day, but g man is asking when you store values in the drop down do those that values permanently stay there so that other people then updating the list in the future that they are they are safe there, okay, yeah that's a good question, so if you think about the data validation, think about it kind of like how you think about bolding something or changing the font or something like that where it's actually kind of baked into the cell so if you were to send this to somebody else it's actually programmed and configured toe have that information in there so if you were to send this to somebody they would see all the same stuff okay now if you are highlighting it and importing it from another file some like that there could be something getting lost there because it is still looking for looking for that cell value somehow but it does see it the same way if you do have it if you just type it in when you go to the dialog box and you're typing everything in you'll actually see all that stuff right there or you're using your list and you type everything in into their you know might be east west, north south all that kind of stuff that that comes configured with the cell itself and that comes shift with its over his emails someplace else right on david is asking I mean obviously what we're doing here is very sophisticated we are getting into now a lot of data but what point essentially does excel run out and you really have to move to access which people are familiar is kind of the database now uh that's a great question okay, so the question about access vs um excel um access is what's known as a relation a ll database system okay, so we have databases than we have relational databases excel it could be considered a database on some levels but it does not have the ability to be a relation a ll database so relational database means that you might have more than one set of data and it might relate to itself and our late to each other in different ways an example of that would be if you have if you own a small business that sells things you're gonna have a database for your products you have a database for your customers and you might have a database of things sold. All right, so there's gonna be three different databases that are going to relate to each other somehow okay, so you might relate them through customer number because each of those individual things will have a customer number. Okay, so what we're talking about here is how to each of these things and to relate and when you have three separate items like I was saying the invoices you might have the product number and you might have a customer number you have a relation a ll database data have them all relate all right based on whatever criteria you want them to relate with so it's a lot more sophisticated, so if you want to look at more sophisticated answer question more directly in terms of when do you stop using excel and then you go into teo? Access is when you are starting to want to see relationships between different parts of your data and analyze it. So with an access you can create things like queries, you ask a question, well, what happens if I am to relate this with that with that? And then you see some kind of report from that so you could do those kind of things access also allows you to put things in a form for matt f o r m, where you're allowed to actually put stuff in a very neat format, so when you have more sophisticated information and you're just wanting to put that kind of stuff in that's more sophisticated and it relates to other components, then you'd be using access because access essentially is a bunch of excel files, not really tacular excel files, but a bunch of tables is what they call it and all those tables all into relate with each other, so that's that's essentially when when you were wanting to use that, but there, of course, there is some overlap, so you could be using using one independent of the other and still get the same result, but it's more when you're gonna get into more sophisticated kind of data input in data analysis, okay, good, good question, anything else? They were good to go there's some last questions they're coming in a little bit mohr sort of generic about things that I think people like so maybe like wrap up questions will say okay very good yes alright coming I was wondering does excel support regular expressions? Um welding equivalent I mean, are you talking like visual basics and that kind of stuff for you have within the formulas what's an example like we're going to into her formulas is that what you mean or uh just out of curiosity because with an irregular expression you could you know, formulate any anything you want like I want a number that's tend to just long with only certain values? Well, you would probably need to either do a macro for that or you need to do visual basic v b in order to create that and you can actually have visual basic visual basic and macros are essentially the same same thing because when you do a macro it uses visual basic which is the programming languages actually behind a lot of microsoft products including excel and word in all these things are actually behind the scenes stuff you know, visual basic you khun do somewhere these complex things built into it so the answer is yes, but we won't be getting into that this class ok? All right very good um all right, so there we are so we've done ah validation in terms of the drop down we've done validation terms of a drop down typing it in are on our own we've done a validation dropped down importing it from another list that we currently have we've also done validation in terms of aa number range for our zip code all right and we actually just typed in the first part where it's going to start and the second part where it's going to end there's gonna be a range of how much because the lowest it could be and the highest it could be and then we also have for dates okay so you wanna have a start date to an end date all right and then you also have the notes so I just calling it notes but it could be just general text how long it could be all right in terms how many text characters you want it to be okay and then of course I showed you how you could do the input message as well as the air messages it's coming up okay so we see all those things that we can do so this is going to be on the on the mat on pc side so we don't have to jump over to the max side and we'll see that we actually did some of this already on this side so let me just move this over and he's a little review to move over a little hand you can move that there because maybe we want to use that a little bit so again, we just highlight everything you're going to see not too much difference in fact almost exactly the same. All right, so what I'm going to do now is simply governor my validate you see, here is all my stuff. All right? So we have a whole value hosting the whole number decimal list date time all that kind of stuff's going to make a list we see here is my cell dropped down all that, okay? And I believe this was just region. So it's the same exact thing east, west, north and south. I'm gonna leave one out this time so I can type in central and see what it's going to do for me. I have all this other stuff here and my my input message and I could actually do I'm going to just money, going to do anything, remember? Because it was a bit annoying, all right? And then I have my error message, and then you can also choose to not show it if you don't want to, okay, so which basically in the gates actually putting it in you just understand that that's there for you, if you want, okay, so again, let me just confirm here separated out by commas it's my list that's good click okay and then there it iss okay just like that magic and then if I just type in just go north and then south also understand when I just type out what I'm looking for us too and I see that comes right up and I could do that actually might be a little faster after you've done that now when I type out central I get this our message the value you entered is not from the required list. Ok, so again, it's the same thing is what we've been doing already with the pc side that we can do on this side as well. Okay, on the on the mac side as well, okay? And we'll do a couple more example shows she can see once again what to say dates entered I'm gonna highlight all these guys are highly whatever you wanted to be and then make sure it's going to be the day that you wanted to be a click on validate dictate a validation and again there you see I have a date and it's the same exact thing date between started one day and it could be any of these things right equal to not able to greater than greater than just basically has to be after a certain date and anything after that will be fine less than it has to be prior to the state even though we're talking about numbers we wouldn't say that in real life we probably say later then but they're saying greater them for less than it's the same thing all right? But I'm gonna go out and keep it as between and then let's just go ahead and do something for next year okay? So we have that ready to go that's great look okay and then what is going to test it out? So we'll say one five fourteen that's great works well and this time I'm going to say twelve thirty one thirteen and I get this our message okay, so you see how all that works no difference on the pc on the max that is on the pc side all right? And you see all the same thing with everything else so we'll go in all the details, but again, I want you to see all the same options and and how you can use this stuff. Okay? So any questions or we what we do? I mean, maybe this is a good time to ask you we certainly can cause serious ale w which might be under lloyd webber. I'm not sure they're saying the reason I always hated excel was because I could never figure out how to print my worksheet showing the formula's because they wanted to proof read them so has that change can you now show the formulas in the cells and not the values I can't imagine how you do that yes, I remember that isn't improvement yes let me I'm just going to create a formula let me just see here I haven't done that for a little while so just give me a second let me just put in a formula here and you can see that there um okay under formulas. Okay, there it is right so okay and then you just drink it okay? So that you're on the mac side yeah, okay it's the fourth one from the fish one cover to the other side okay, so we're gonna go formulas formulas not seeing in here where my my blind you see here it's the fourth this is his show get back quickly. So is that and there you just creamed it on your formulas here? Are you talking about on the tab or inside the preferences here show and then show formulas and just printing right from here? Oh yeah, that way we have the formula just make sure that that's going to do that okay? So I have just go out and do a that's going to be there, ok? Right? So the formal actually just shows right there and then let me make this so it's going to be a bit to one page a case that should be there I'll do it kind of gets squeezed in there okay, I do my print preview can't see too well here let me to zoom into well um let me come back to the pc said a lovely to zoom in a little bit better okay, so for me was so formulas okay I think that there's a there's a shortcut key for me there was a short count kids it's control it's actually a little a little character underneath the escape key and if you can see that there so that allows you to toggle back and forth between it I knew that there was a formula to be able to do that sees me a shortcut key be able to do that I just control and that little icon right here you can see it's coming on my screen that little guy which is running to the escape key. Okay, so now uh let me go ahead and just do a print preview for this so we can take a look at it and we're wasting printed number isn't it not the expected result not the formula. So unders this's the main one under zip code and we go ahead and just do one where you can actually more obvious. See, here it is you can see it here so this is this is the print preview was way at the end sorry, I should have actually done a page um scale to fit it. Let me just make sure this is all all on one page is a lot easier to view okay, now you can see it you see it down there can you see it there? So that's how it's going to print great you're showing the formulas that was a tricky one. Okay, that was a tricky one. Yes. Thanks for good. I actually didn't realize you could do that a tour I thought once you put the you know the formula in yeah yeah, you were really only ever to see what was what the result wass yeah? Can you also when you add notes to a field I think you did say this is a can you then see the note when you want to print up? Yeah that's also going to be under that was under comments we do comment yesterday that's going to be under our page layout and page set up or we go to sheet on the page set up dialogue box we're going to go ahead and to say comment all right as displayed on sheet and you can print them right from there of it. Yeah, ok, good question I like being stung because usually means that I learned something that's my favorite part of what I do is I learned something as much as I help other people learn so that was great great thank you can't feel help yes my list on the side of my spreadsheet and everything disappear. Yes, that that will happen exactly, yeah, tried to kind of create that. So you kind of began have one of those sort of, like, many traumas, so you kind of experience that was like, oh my god, where is everything that is going in empty, drop down? We have control z, right? We have controls your commands, e. Exactly.