Last week I mentioned that there were some refinements that could be applied to the greeting card inventory to take advantage of the fact that it’s in a spreadsheet, not on a piece of paper.
Option 1: Last sent date
If you look at last week’s example, you might notice that it’s a little hard to tell at a glance when the last time you sent someone a card is. That’s the disadvantage of having the years in the cells, instead of the column headers. However, you can overcome this by adding a column somewhere (I recommend inserting it between the people’s names and the first column of years, or else placing after the last column of years), and using the max() function. For example, if your card columns are column C through J, then your formula in row 2 would be =max(C2:J2).
Since the most recent year will always be the largest one, that will tell you the most recent year you sent that person a card. It’s particularly helpful when you’re scanning through and trying to figure out if you’ve assigned a card to everyone.
Option 2: Cards left
You can also have your spreadsheet tell you when you’ve assigned the last card, so you don’t decide to send out 2 of a card that you only have 1 of. To to this, you’ll need to enter insert rows. I actually recommend inserting three rows above the column headers, and just using the first two. That will let you sort the table of card information if you ever want to, without it trying to include these formulas in the sorted data.
Then, in the first row, enter the original number of cards. (Or, if you’re starting this spreadsheet partway through a package, enter the current number).
In the second row, use the counta() function. Since counta() just tells you how many cells have something in them, =counta(C5:C100) would tell you how many people you’d sent the card in column C to (assuming your list of people doesn’t have more than 95 people in it. So the actual formula you’d want in column C would be =c1-counta(C5:C100).
You can also use conditional formatting to draw your attention to it by making the cell background red if the value is equal to or less than zero.
Option 3: Stamps needed
Another thing you could do quickly and easily is use =countif() to figure out how many stamps you’ll need to mail the cards you intend to send this year. If you want to update the formula every year you could use =countif(C2:J100,2013), or if you want it to automatically adjust for the current year you could use =countif(C2:J100,year(today()).