Wednesday, December 5, 2012

Spreadsheets and Christmas present shopping

My head is spinning and I have to write it down before I explode. Have been working on spreadsheets today. Personal ones. Firstly, last night I chased up everyone that is coming to our Christmas lunch (we started this a few years ago and its become a bit of a tradition - its basically a lunch between any of our friends who want to come). This year we have 25-27 people plus a few toddlers. I have a spreadsheet of course. Whcih is slowly evolving. Previously it was just a list of names. Now its got names, number of adults, number of kids, totals column (which auto populates from the previous 2) then RSVP column. Then another adult/child/total set of columns that auto populate ONLY if the RSVP column is yes. Also if RSVP is yes, then the row for that person or people goes green. If no, then red. If maybe, then orange. Gotta love a conditional format. I have a column for whether or not a high chair is needed (by the way above the header of these columns I have a count formula that gives totals - so running at the top of the columns I have total adults invited, total kids invited, total same but definitely coming, total number highchairs required). Then recently as I chased people up to find out what menu people want to go for - there are 3 set menu choices and an a la carte menu plus a set kids menu. So I have a column for each of these and depending on which one they choose I put an "x" in the box. There is then a column each for starter, main and dessert so I can put their choices in there. I also have next to each of these a column for price (just in case they are ordering from the a la carte menu (although the restaurant hasn't sent me a menu that has prices on it so I have no idea of this right now). Then I have a column for total price which is a formula based on a nested if statement (or a nest of if statements - not sure what the correct phrase is) which calculates the price depending on what menu they have chosen. Have a column for deposit paid (£5 per head and I put that in when they have paid) and then a final column that works out how much they need to pay on the day in the restaurant. Groovy eh?

I have also been working on a christmas spreadsheet - this contains a xmas card list (not just ANY list but a list of names - imported from my contacts - with a column each for if they appear on my xmas list, if the card has been written, if its been posted and ALSO a column for if a card has been received from them (so I remember to send one back if they were not already on my list). It also has a priority column which is, you guessed it, colour coded. THis is so that I can filter it by priority (1-5 - 1 being highest priority - ie close family) and do those cards first. And if I don't get around to the 5s then the world isn't going to end. In actual fact I used this format last year and this year I actually removed pretty much all of the level 5's. Life is just to short. Especially if you never get one back. I also have a tab in this spreadsheet which has a pivot table which gives me a status summary of how many cards I have to send (45 this year), how many I've written (23), how many I've posted (17) . I also added a percentage bit. I'm currently 51.11% done on writing them. 37.78% done on sending them. I have a christmas present buying budget tab which lists all the people we are buying presents for, how much we want to spend, how much we've spent (this is pulled from another tab - which shows what I've spent on each person) and how much is left.

Suffice to say I have been looking at these all day and making sure they are up to date and trying to find xmas presents online for family members (most of whom seem to want bizarre things that you can only buy on a floating shop in a boat in the middle of the congo (no idea if there is water in the middle of the congo but you get my drift). CAN'T I JUST BUY YOU A BOOK OFF AMAZON!!!??????

No comments:

Post a Comment

Please feel free to comment but please be aware my blog has now moved to so I'd love it if you'd come visit and comment there.