In the last few days I've found a couple of wonderful tools.
The first was in Google Apps where I can create a blank spreadsheet, then use their "create form" tool which automatically creates the column titles in the spreadsheet from the field names you specify in the form creator. Then you save the thing and get the html to embed the form in your web page. As people fill in the form it writes to the spreadsheet. You can stick the form in multiple places if needed and collect the data in one sheet. Beautiful
The other I just found in Open office. I love spreadsheets but I'm a total bunny when it comes to formulae, especially the complex ones.
Yesterday I promised my wife I would create a sheet to track our expenses so I downloaded the details from the bank and went through the list coding the transactions for house, groceries, cars, dogs etc. Then I hit the problem, how do I tell the sheet to look up the categories in column E and sum all the amounts in column B that have matching categories?
The actual formula is mind-bending to this turkey but OO has a thing called Data Pilot which is a total joy. Go to Data/DataPilot/Start and you get this interface.
The column titles are listed on the right, you drag them to where you want them, so I pull the category into the Row Fields column and the Amounts into the Data Fields (the default is "Sum" but you can choose average, count, etc as operators) and click OK.
OO creates a little table underneath the data that sums each item by category and we are done.
Elegant, simple, fast and free to boot, delicious.
Now what I want to know is why can't I do all that on my bank website? I should be able to tell it that all transactions at BP, Mobil, Shell etc are petrol, all Countdown, Pak'n Save, etc are Groceries and so forth so that when I login I can see a little chart that tells me how much we have spent in each category for the last month, quarter and year (or whatever).
OK, I'll ask why not. Back sometime with the answer.
you can do the same thing in Excel using the obscurely-ame Pivot Tables tool, under the Data menu
it lets you do a lot more than just Sum, too. double-click on the things you've dragged to various places to see your options
Posted by: Saltation | October 04, 2008 at 08:01 AM
^ame^named
Posted by: Saltation | October 04, 2008 at 08:05 AM