In the Club
Run a club or society? Here's how your tech can make it easier
Manage a membership list using a spreadsheet
Keeping membership records updated is vital, and while a dedicated address-book program can help, it’s often easier to use a spreadsheet. By listing everyone on a spreadsheet you can sort them by any parameter you choose and use a feature called conditional formatting to highlight records that need attention. We’ll be using Calc, the spreadsheet component of the newly updated Libreoffice 6.1.4, but the same instructions will work with Microsoft’s Excel.
Set up your spreadsheet
This kind of spreadsheet works best if you put each member on their own row, with the columns used to display personal details. Decide which details you need, and list them in row 1. As you can see from our screenshots, we are recording first and family names, address and birthday. We’ve also added a column for the date when each member’s record was last updated.
As long as you include this column somewhere in your spreadsheet, you can choose whatever you like for the others. When you first create the spreadsheet, every date in the ‘Last update’ column will be the same, but they’ll change over time as you amend records. Double-click the tab at the bottom of the spreadsheet and use the box that pops up to rename the sheet ‘Members’ (see screenshot below).
Calculate a record’s age
To create a new page, click the ‘+’ at the bottom of the spreadsheet, beside the Members tab. In cell A1, which will be pre-selected, type =TODAY() then press Return. The formula will immediately be replaced by today’s date. Although you could have typed the date manually, using the ‘=TODAY()’ formula means the date will update automatically every time the spreadsheet is opened. Double-click the tab at the bottom of the sheet and change its name to ‘Today’, then click the Members tab to switch back to the first page.
Click in the first empty cell in row 2 (cell F2 for us, as you can see from our screenshot below left) and enter the formula =SUM($TODAY.$A$1-E2), (replacing ‘E2’ with whichever cell in your spreadsheet contains the date of the last update on that row).
This formula tells Libreoffice Calc to retrieve the contents of cell A1 from the ‘Today’ page and subtract from it the contents of cell E2. The ‘$’ before ‘Today’, ‘A’ and ‘1’ tell it never to change these parts of the formula if we paste it into another cell. And because we haven’t used ‘$’ before ‘E’ or ‘2’ in the formula, that value will automatically update.
In Excel, the formula is slightly different and drops the first ‘$’: =SUM(TODAY!$A$1-E2) Press Return and Calc (or Excel) will calculate how old the first record is.
Highlight old data
With the cell (E2 in our case) still selected, Click the Format menu at the top, hover over Conditional, and select Condition in the pop-up menu. Leave the first menu set to ‘Cell value is’ and select ‘greater than’ in the second menu. In the box at the end of the row, type 183. Doing so instructs Calc to only apply the formatting you’re about to specify if the number in the cell is 183 or higher. Why 183? Because half a year is 182.5 days, so if the cell reads 183 or more the record has not been updated in the past six months.
On the line below, click the menu that initially says Accent and select Bad from the list of options (see screenshot above top), then click OK. If the record is more than six months old it will now be highlighted in red (see screenshot above). If not, it will be black text on a white background.
If you’re using Excel, click Home on the menu, followed by Conditional Formatting. Hover over Highlight Cell Rules on the menu and click ‘Greater Than…’ in the pop-up menu. Enter 183 in the first box and select ‘Light Red Fill with Dark Red Text’ in the dropdown menu, then click OK.
With the cell still selected, press Ctrl+c to copy it, then paste it into every cell in that column at the end of each populated row. The age of each record will be calculated and the highlighting applied to records not updated in six months.
You now know which records need to be checked. Don’t forget to change the date in the ‘Last update’ column beside each one as you verify them, and the formatting will be removed for the next six months.