Building a flexible monthly table using functions

- In this worksheet, we're setting up a data entry vehicle for someone who doesn't necessarily know a lot about Excel. Ultimately, all this person or persons will be doing is selecting a month in cell A2, and as I click there, you'll see a drop arrow, it's a picklist, I'll show you how to do that by way of data validation. Same thing in column B, in cell B2, we pick a year, and depending upon that choice, we want our display here to reflect how many days there are in that month. Now, I've hidden some of the rows ahead of time because, as we look at this model, we want to be viewing some of the early days along with the other data here, and then the later days toward the end of the month.

So you'll notice that rows 14, and then we see row 29. The other rows have been hidden. So, let's talk about how this is set up. And a lot of different functions are in use in this model, including the ones we see over here. These are simply here for reference purposes; eventually, we'd get rid of them. The list that we see in columns J, K and L ultimately will be hidden. We can move these to a different worksheet, or hide the columns, as we choose. The user of this worksheet will simply put data, by way of choice, in cells A2 and B2 and then fill in the information here in the relevant green cells.

So, how is this all set up? Let's begin with the idea of the picklist. Instead of having somebody type a month or possibly mistype it, we simply have made cell A2 be dependent upon this list over in column J. The list is there ahead of time. How so we set this up: we click on cell A2 and then go on the Data tab to Data Validation to the right, and if we had not made the choice earlier, we would choose List, right here, under Allow, and then, in the source panel, right here, (selects the cells populated with month names) and click OK; all set up.

And then you would test it out, of course, scrolling up and down, make sure they're all selectable. Select this one, select that one, and so on. Year is done the same way, I won't go through the details, but there, the list is over in column L. And of course, that could be a longer or shorter list. So we're only allowing the years 2015 through 2025 to appear in our model. The key to making this all work is, once we have chosen the month and the year, we want to make sure that the day of the month that's being displayed here doesn't exceed the number of days actually in the month. And of course, some months have 31, never any more than that, but occasionally it's 30 and, worse, occasionally, 29, once every four years for February.

The green box here is simply what the user will see. The user will enter data in cells A2 and B2 by way of those picklists, and then type in the numbers over here. The user will never see these formulas, although, he or she could by clicking, and so on, but we want to talk about these formulas one by one as they're set up. First of all, in cell A4, the date function allows us to create a date by gathering a year and a month and a day from different locations. The year is gathered here, from cell B2, that's the year that you would have put in with the picklist.

The month is going to need to be gathered by using a VLOOKUP function, which we'll get to in a minute, and the day that we want here, for the beginning day of the month is simply the first, so that's why the one is there. Let's focus on the VLOOKUP function. The VLOOKUP function allows us to take information, for example, cell A2, right now contains the word "August", we want to be looking in columns J, K, off to the right together, and when we find a match, we'll go into the second column to pick up the associated number. So, for August, it's eight, January is one, February is two, and so on.

And zero here simply means we want to have an exact match. Those of you familiar with VLOOKUP know that we could be using the word "FALSE" here; you wouldn't know that intuitively, but zero, or "FALSE" means exact match, meaning we must find that entry, August there. So this does its work, and it brings back, in effect, the year 2016, the month eight, the first day, that's the date. Now, we need to show the end of the month because ultimately, in column E, we want to be testing these entries to make sure we're not exceeding the number of days in any given month.

So, this function in cell B4, EOMONTH means "end of month", and often this is used to look forward or backward. If we put a three out here, we're going to find the last day of the month three months out. And so, for August, this would return, for example here, November 30th. If we put in a minus three here, going back three months, that would be May 31st. But if we put in zero, we're saying, in effect, don't move into any other month, stay in the current month, give me the end of the month entry, in other words, the last day of the month.

Now, we can use the DAY function to pull out the last day here, so we know, of course, we know outside of Excel that there's 31 days here, but, if we use this DAY function, here, simply on this cell for the moment, it's going to return a 31. And, of course, if we use a different month here, like September, which has 30 days, of course we see the 30 there and the DAY function here picking this up. Now, we're going to be using this in column E here, and all we're trying to do here, really, is to get the day of the week. And so, in the core of this, we see the WEEKDAY function.

Now, most of this IF function, at least the initial part of it, is designed to recognize if we've gone beyond the end of the month, in other words, too far. So mostly what we're doing in column E is simply picking up the weekday. And we've got to go through a little bit of, I guess you'd call it shenanigans here, or something, and this is not obvious, but, if we pick up the weekday, in other words, the day of the week, Monday, Tuesday, Wednesday, et cetera, whatever that might be, from cell A4, that's the beginning of the month, if we then add what we see in cell D2, this will take us one day beyond what we need.

But the point is, we want to be using the numbers that are in column D, those are just numbers that were typed in, but as we're moving down the column, we always want to pick up the correct weekday. Now, as you're setting something up like this, you will either need to subtract one, or add one, or maybe do neither, but, of course, when you come up with an answer here, you want to say, is that correct, and maybe have your calendar in front of you, paper calendar, whatever, you're making sure that this is working. If I change this to January, notice how the days of the week are changing. January 2016, January 1st, beginning of the year, was a Friday, there it is, you see it there, but this first part of the test, the IF function, really only is relevant as we get down toward the end of the month.

So, for example, as I switch to February here, February 2016 has 29 days. But what is our logic saying down here, for example? In this cell right here in E31? If the day of B4, in other words, if the last day, if the day of the last day of the month, in this case, it's going to be 29, is that less than what we're seeing in the cell to the left? So this portion of our formula right now, has the value 29. is that less than what we're seeing over in cell D31? Is it less than 30? It surely is, so what are we displaying? Blank.

Double-quote displays blank, so that's what's happening here. Now, keep an eye on these three cells here as I switch different months, from February over to March, for example. March has 31 days, so we see what's happening. April, 30 days, we see what's happening there, and so on. September, 30 days, same idea. And we're picking up the correct day of the week all the way through this. So, the user who sees the instructions will only enter data in the green cells. Now, what if you try and put data here? Put some data there, message pops up, doesn't match.

Now, this message could have been customized to explain why, but this is done by way of data validation. So, when could these be empty or blank? Possibly it's February of a non-leap year, I'll change that year there to be 2017, change the month to be February, if we try and make an entry here, that's blocked. So it's only blocked when we try and put data in these cells here for days that do not exist. So let's look at the Data Validation rule for these cells right here. Cells are selected, Data tab, Data Validation, and the rule is, and you may not have seen this function, it's called LEN, and it means length.

Notice how the dollar sign is in front of the E but not the 30. This formula, in effect, is saying, looking in column E, and if you're in row 30, look in E30; if we're talking about the cells in row 31, we'd be looking in E31, but if the length of the entry there is not zero, in other words, if it contains Tuesday, Monday, Wednesday, whatever any of those letters that we see in column E, data's OK, in other words, we can make entries there. When it's not zero, in other words, when the length is something other than zero, bigger, so, that's the rule in effect.

I can't type anything in these cells right now, but, if I switch to March or April, I can't make an entry there, but I can certainly make an entry here, and I won't be stopped. That's working just fine. Now the color that's applied here is done by way of conditional formatting, so all these cells here were selected, and, from the Home tab, Conditional Formatting, almost the same idea, but here we have a rule, it's already in place, if you want to see the rule, you Manage the Rules, you've got formula in place, you can see it right there, you can also edit the rule if you wish, and this is pretty much saying the same thing.

If the entry over in column E, whether it's row 30, or 31 or 32, if that's standard entry, in other words, if it's a day of the week, if it's not zero, if the length of this is not zero, make the cells be green. When you edit the rule, you might see this a little bit more clearly, so what we're saying is, when this is true, in other words, when there is an entry in column E, then make the cells green. And all along, as we've been changing the months you've seen how that plays out. So, once again, jumping back to February here, of a non-leap year, February of a leap year, the 2016 or 2020, let's see how that's playing out, there is a 29th.

So, all these things, pulling all these together, a bit of a challenge. We've seen a lot of different functions here, there's one more set of functions we haven't seen, and that's for the calculation in column H. Now, if there were nothing special going on here but there were a division formula right here, we would see an error right now, because we'd be dividing by zero. The calculation that will normally take place here is simply the dollar amount divided by the number of transactions. And that's the key formula that we see off to the right. But, we don't want to see a big error message there when the cells are empty.

So, the IF function here says, if we've got either of two kinds of errors, we don't want to display anything, so we're displaying with double-quotes, blank. So, what are the two errors we're looking at? If the division here causes an error, so we're saying, if there's a division by zero error, let's display blank. Or, if we see that the entry over in column E, if the length of it is less than zero, now, this too, this portion of the formula doesn't have a lot of meaning for the early days of the month, it only has meaning, for example, as we get down here, into those last three days.

So, for example, here, if the entry over in cell E30 happens to be a zero, as it is right here, then we've got an error, we don't want to display anything, just a double-quote. Ultimately, this column only needs to be, you know, roughly that wide, depending upon our answers here. So, as we start to put in numbers in normal situations like this, we begin to see our answers over here, and there's the per sale amount and so on. And this will work for all these cells. Remember, we can only fill in the green cells. So a lot of work takes place ahead of time, but ultimately, the user of this wouldn't be seeing these, we'd get rid of that, we'd probably hide this or move it to a different sheet.

The only problem here might be that whoever finishes using this, should, after having printed this or saved it or copied this, simply scour out or empty out these cells. And then, the next user who comes along, or maybe it's the same user, will put in a different month and a different year. So, quite a few different tools being used in this example: DATE, VLOOKUP, EOMONTH, IF, DAY, WEEKDAY, ISERROR, OR, LEN, also Data Validation and conditional formatting.