Do you need a reusable sign-in sheet to manage your coffee club? Look no further.
Here is a printable sign-in sheet you can use for your coffee club members to sign in whenever they take a cup.
It has a few Excel features you might consider using, for example
- Conditional Formatting to make cells appear dynamic, w/o VBA
- Data Validation
- ActiveX Controls
- Deliberate Errors
- Custom Lists
How It Works
First, I set up a sheet called Months and used the built-in Custom List feature to fill down the month names in column A. I highlight the range and type "Months" in the Name Box.
Normally I would use a dynamic named range here, but since the month list is unlikely to change, I feel comfortable hard-coding the range.
The Months sheet is hidden (Format » Sheet » Hide) to keep it out of the way. We won't need to see it any more.
Now I set up another sheet called Names and create two columns: column A contains the names of people in the coffee club, column B is a list of years. Using the dynamic named range VBA function, I give both ranges dynamic names as follows:
Range Name: Names
Formula: =OFFSET(Names!$A$1,1,0,COUNTA(Names!$A:$A)-1,1)
Range Name: Years
Formula: =OFFSET(Names!$B$1,1,0,COUNTA(Names!$B:$B)-1,1)
I use dynamic names here because I will want to add/remove names and years. When I want to do so, I only need to edit this list.
Now I create the sign-in sheet and add two controls: a listbox and a combo box.
The steps are:
- Display the Control toolbox (View » Toolbars » Control Toolbox)
- Click the listbox and then draw it somewhere on the worksheet using your mouse.
- Click the combo box and do the same.
I chose an out of the way spot (column AJ) which I can easily view electronically, but won't print.
Right-click the listbox and go to Properties. Under LinkedCell I chose AI1, and under ListFillRange I enter "Months" (without quotes). The listbox will be the list of months, and the selected month will be printed in cell AI1. I name cell AI1 CurrentMonth.
Right-click the combo box and go to Properties. Under LinkedCell I chose AI2, and under ListFillRange I enter "Years" (without quotes). The combo box will be the list of years, and the selected year will be printed in cell AI2. I name cell AI2 CurrentYear.
Number of Days In Current Year
Now I go back to the Names sheet and enter the following formula into a scratch cell:
=DAY(DATE(CurrentYear,MONTH(1&CurrentMonth)+1,1)-1)
This cell is named NumDays. To figure out this formula I started at Calculate The Number Of Days In A Month and browsed until I found the solution. I can't remember the website where I found this, if anyone out there can find it, let me know.
What this formula does is calculate the number of days in the currently selected month. That is, the month and year selected in the list- and combo boxes on the sign-in sheet! We've linked all three cells (CurrentYear, CurrentMonth and NumDays) so that when we choose a month and/or year, the number of days in that month/year combination will be available in the NumDays named range. Shortly you'll see why we do this.
The Sign-In Sheet
In the header row I use the following formula:
="Coffee Club Sign-In Sheet For "&CurrentMonth&" "&CurrentYear
This formula will always show the year and month selected in my combo- and list boxes.
In row 2, I fill numbers 1-31 across columns C:AG to represent the days of the month.
Conditional Formatting
At most we'll need 31 days of coffee for any given month, but we don't want to see 31 days all the time. In February I only want to see 28/29 days, in September 30 days, and so on, so my club members don't get confused. So we'll use conditional formatting to selectively hide the unused days.
Remember that the number of days for a selected month/year combination are stored in the NumDays named range. So I'll select all 31 days (C2:AG2) and use the following conditional formatting formula:
This formula, when applied across columns, will change column reference while the row reference stays the same (due to the '$' dollar sign absolute reference). The format is white-on-white. Any cell that is greater than the number of days in the given month will be whited out!
So in August, you will see all 31 days, but in September, the 31st day will disappear.
The Disappearing Names and Numbers Act
To create the illusion of self-updating numbers and names, we use the following formulas (starting from row 3):
Number: =COUNTA(Names)-(COUNTA(Names)-(ROW()-2))
Name: =INDEX(Names,$A3)
The Number formula is filled down from cell A3 to A52, to create a possible 50-member coffee club (feel free to add more if you have more!). However, conditional formatting is applied to this range (A3:A52):
What this means is that any number higher than the count of names will be whited out. So when you add or remove names from the Names sheet, this list appears to auto-update! Really it is the conditional formatting doing the heavy lifting.
The same thing happens with the names list. cells B3:B52 are filled down with this formula:
=INDEX(Names,$A3)
You'll immediately notice that this formula causes an error when it passes the maximum number of names on the Names list. How do we fix this? With conditional formatting of course! The following formula is applied to this range (B3:B52):
Any errors will now be whited out.
So when you add/remove a name from the Names list, the index number will appear as if by magic, and the name will appear on the sign-in sheet. All with no VBA code whatsoever!
On my sample worksheet (see below for download link), data validation is used in the data entry area, which assumes that each person takes one coffee per day. This is hopelessly optimistic, so to change that simply remove the validation and expand the width of those columns (C:AG). Or you could add additional validation options:
- X
- XX
- XXX
- XXXX
- normal
Ideally the form should be printed and filled out manually, but how you use it is up to you.
Download Coffee Club Sign-In Form for Excel 2000-2003
Download Coffee Club Sign-In Form for Excel 2007/2010
