Matthew from Washington wants to take an event database and create a visual calendar that shows availability.
While this sounds simple, it requires several steps. The end result is fairly cool. Episode 717 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
While this sounds simple, it requires several steps. The end result is fairly cool. Episode 717 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today a great question came in from Matthew.
Matthew manages a club in Washington, D.C.
And he says hey, I need to be able to see at a glance, which days the club is reserved for an event and so, he has this worksheet here that has a column it has date.
I'm sure he has many other columns like who reserved it and details and things like that, but we want to see if we can build a calendar, that will highlight the dates that are reserved.
Well, that sounds simple to do it's actually several different steps. We're going to insert a worksheet and I'm going to enter a Sunday date here 3/2/2008.
First of all I'm going to enter formula that will add one to that date as we go across and copy that across seven cells and then in the next row basically, we're going to take the date above us and add seven, and I'll copy that down for a however far you wanna go.
If you want to have a calendar, that shows four or five weeks.
Or if you want to have a calendar, that shows the whole quarter.
You can do that now.
Let's do some formatting to actually make this thing look like a calendar.
I'm going to choose all of the cells and we'll go to Format Cells, couple of things I wanna go with a small font, nice small font on the Number tab I'm gonna change to a Custom number format that just shows me the month slash day.
On the Alignment tab I want that date to be in the upper right hand corner.
So, with Horizontal we'll go with Right, and with Vertical we'll go with Top.
On the Border tab let's add outline and inside and the last thing to do here is to make the cells much taller.
So, if we go to Format, Row Height and maybe 45, kind of get the things starting to look a little bit square.
I wasn't smart enough to add the days let's put Sun, here we'll center that and grab the fill handle and drag to the right.
So, we can see which day of the week it is.
Okay now, We're ready to highlight the cells that have a value and unfortunately, Conditional Formatting generally says that you cannot refer to sell us on another worksheet, but we can beat that let's come here to the Events tab and I'm going to choose the column that has the date.
So, I choose that column, and then I click here in the name box and type a name.
So, in my case I already call this my dates, no spaces of course my dates press ENTER.
And we now, have a named range and the beautiful thing is that name range can be used on another worksheet.
All right so, let's come here and we're going to build a conditional format.
I'm going to do it for this cell first and then copy it to the other cells.
So, we choose Format, Conditional Formatting and we have to change from Cell value Is to Formula Is =NOT(ISERROR(MATCH(A2,MyDates,0).
So, basically the match is either going to return a row number if it's found or an error if it's not found.
The is error and not basically will force true to appear anytime that we have a date booked.
I'll go to the format tab and choose whatever color. I want maybe red or yellow, click OK, and initially we see nothing happened, but I'm going to copy that cell select all of the cells in the calendar and use Edit, Paste Special and Formats, click OK.
And sure enough, we have several cells that turn red because the dates are booked.
Now, let's test to make sure that actually works if we go back to the events, and we had a brand-new date maybe 3/17/2008, book a st. Patrick's Day party.
Of course add some data in and now go back to the Calendar tab. You'll see that sure enough 3/17, is now booked and the beautiful thing about this is as time goes by let's say that we move up to April.
So, the first Sunday in April is April 6th. We might go here and type 4/6/2008, because of all the formulas the calendar automatically updates, and we're now looking ahead at the next 13 weeks.
Boy, what a great question for Matthew easy to do although it requires several different steps.
Oh hey, wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today a great question came in from Matthew.
Matthew manages a club in Washington, D.C.
And he says hey, I need to be able to see at a glance, which days the club is reserved for an event and so, he has this worksheet here that has a column it has date.
I'm sure he has many other columns like who reserved it and details and things like that, but we want to see if we can build a calendar, that will highlight the dates that are reserved.
Well, that sounds simple to do it's actually several different steps. We're going to insert a worksheet and I'm going to enter a Sunday date here 3/2/2008.
First of all I'm going to enter formula that will add one to that date as we go across and copy that across seven cells and then in the next row basically, we're going to take the date above us and add seven, and I'll copy that down for a however far you wanna go.
If you want to have a calendar, that shows four or five weeks.
Or if you want to have a calendar, that shows the whole quarter.
You can do that now.
Let's do some formatting to actually make this thing look like a calendar.
I'm going to choose all of the cells and we'll go to Format Cells, couple of things I wanna go with a small font, nice small font on the Number tab I'm gonna change to a Custom number format that just shows me the month slash day.
On the Alignment tab I want that date to be in the upper right hand corner.
So, with Horizontal we'll go with Right, and with Vertical we'll go with Top.
On the Border tab let's add outline and inside and the last thing to do here is to make the cells much taller.
So, if we go to Format, Row Height and maybe 45, kind of get the things starting to look a little bit square.
I wasn't smart enough to add the days let's put Sun, here we'll center that and grab the fill handle and drag to the right.
So, we can see which day of the week it is.
Okay now, We're ready to highlight the cells that have a value and unfortunately, Conditional Formatting generally says that you cannot refer to sell us on another worksheet, but we can beat that let's come here to the Events tab and I'm going to choose the column that has the date.
So, I choose that column, and then I click here in the name box and type a name.
So, in my case I already call this my dates, no spaces of course my dates press ENTER.
And we now, have a named range and the beautiful thing is that name range can be used on another worksheet.
All right so, let's come here and we're going to build a conditional format.
I'm going to do it for this cell first and then copy it to the other cells.
So, we choose Format, Conditional Formatting and we have to change from Cell value Is to Formula Is =NOT(ISERROR(MATCH(A2,MyDates,0).
So, basically the match is either going to return a row number if it's found or an error if it's not found.
The is error and not basically will force true to appear anytime that we have a date booked.
I'll go to the format tab and choose whatever color. I want maybe red or yellow, click OK, and initially we see nothing happened, but I'm going to copy that cell select all of the cells in the calendar and use Edit, Paste Special and Formats, click OK.
And sure enough, we have several cells that turn red because the dates are booked.
Now, let's test to make sure that actually works if we go back to the events, and we had a brand-new date maybe 3/17/2008, book a st. Patrick's Day party.
Of course add some data in and now go back to the Calendar tab. You'll see that sure enough 3/17, is now booked and the beautiful thing about this is as time goes by let's say that we move up to April.
So, the first Sunday in April is April 6th. We might go here and type 4/6/2008, because of all the formulas the calendar automatically updates, and we're now looking ahead at the next 13 weeks.
Boy, what a great question for Matthew easy to do although it requires several different steps.
Oh hey, wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
