I'm a basic user with spreadsheets. I thought I was average, but after a little time googling possibilities and reading through (usually unhelpful) help docs, I can see there's far too much I'm unaware of!
I'm attaching part of a spreadsheet I've created. I would like to do a report where each expenditure item and value is listed under the month heading. (It's basically reversing the way I've setup the columns and rows in the sheet!)
I.e :
December
BHIA
1250
REFURB
2274
BHIA
1500
There's about 50 creditors, but I've just included a few to get the idea. The totals for each month go to a cash flow forecast on another sheet. So I'm also wondering if there's a way to from each month's 'total outgoings' cell create the same report above, without actually having to go to the expenditure sheet I've attached. (if you catch my drift!)
What varying options do I have and why might I choose one over the other? I guess you can import things into tables in a word doc, into a database and report from there, or using data pilot etc. But I can't in this instance get them to do what I want, nor do I have any experience with them. The database report says I can't use binary fields in building the report. So I don't understand.
If anyone can offer help I'd be grateful. Thanks for your time also.
If you only know the way to do it in Word, then that's fine, because it will be useful for me to know for future, and I should be able to work it out in Calc. But OO Calc is what I'll be using for the most part, due to it's free nature!
Presumably you are wanting to change the layout to make it more readable? With 50 creditors you are going across a lot of columns.
If you are drawing the data in the sheet you've attached from another sheet, then perhaps the best thing to be looking at in OOo Calc is DataPilot Tables. In Excel these are known as Pivot Tables.
I would focus on this area of OOo Calc. A knowledge of DataPilot Tables / Pivot Tables will allow you to summarise and organise the data EASILY in the way you are looking for. Yes, this method is probably the easiest route to go.
That would be my pointer, hope this offers some assistance.
Thanks for your reply. I preferred placing the creditors in the columns as I find it easier scanning down the amounts each month, rather than across.
The data on the attached sheet is actually not drawn from anywhere else. I work with a manual cash book for this company. I needed to do a cash flow forecast for them so put all the regular creditors on that spreadsheet to make it easier to work out a monthly figure than looking through page after page.
I do need to spend some time learning more about Office packages. I can make a decent looking document but when it comes to macros and more in depth formulas, that's where my knowledge ends. I can though see how simple programs like Sage are to program, and can see how someone is making a bomb out of getting there first!
Thanks for your pointer and cross reference to Word. I will look into Data Pilot.
I've not used OOCalc but assuming that you either have Excel available or OOCalc really is as close as it makes out then have a play around with Pivot tables as that seems to be what you are looking for. Bit too complex to explain in a reply but when you've been playing with them for a while you will see what I mean.
being quite a simple soul my personal preference would be to have one worksheet per month and a totals worksheet which is automatically updated by changes to the subsidiary sheets. (you know, linking with something like =Sheet1!B16).
For Excel the book that you really need to get a good grip of this sort of stuff (and one that I swear by rather than at!) is :
Microsoft Office Excel 2007 Data Analysis and Business Modeling. ISBN 978-0735623965. Available from Amazon at £17.11 with free delivery.
In the short term though take a copy of your sheet and use the included help facility to start having a play with Pivot tables. Once you've mastered it you will see how quickly and easily you can create these sort of reports.
Have fun,
Shaun.
-- Edited by Shamus on Sunday 19th of December 2010 11:44:34 AM
__________________
Shaun
Responses are not meant as a substitute for professional advice. Answers are intended as outline only the advice of a qualified professional with access to all relevant information should be sought before acting on any response given.
