I need a few answers

[alt7]

Hello,

I need a few answers to the following problem. Can anyone help me please?
Thank you very much.

All the information is in one file that contains 4 sheets. I will present all 4 sheets.

Sheet 1


Sheet 2


Sheet 3


Sheet 4


Sheet1 and Sheet2 are the source of information for Sheet3 and Sheet4 which I need help for.

First for Sheet3 I need it to work like this:
In the top right I can enter a date (it defaults to current date) and the rows below will be automatically completed with all rows from Sheet2 that are on the date I entered.
Every day will have a value to report to the next day that will appear on the Income column on Report / Previous Balance.
How can I achieve this?

Second thing is in Sheet4.
Here I can enter a start date (default is start of the year) and an end date (default is current date) and it will give me some values calculated by using the date in Sheet1 and Sheet2 as follows:

Balance: this is based on the end date only and would give me the balance for Sheet1 and Sheet2 for the end date (and the sum of them in the total)

Income / Credit: this would return the sum of all the values in the Income column from Sheet2 and the sum of all Credit from Sheet1 (between the given dates)

Expenses / Debit: works as above but for Expenses / Debit columns

Category 1..n this is the sum of all rows from the respective category (as you've seen there is a category column in both Sheet1 and Sheet2) between the given dates.

And one last thing. How can I make white space around the cells, like in the image below. I've seen it's possible.



Any help would be greatly appreciated.
Thank you very much again.

[alt7]

Is this something I can do with Pivot Tables?

Is there a tutorial on this for Open Office?

Thanks.

[Villeroy]

In my honest opinion this should never be done by a spreadsheet.

Before you invest too much efford - please read:
http://www.eusprig.org/stories.htm

On data pilots (aka pivot tables):
They can be used for jobs like this when the source range is a single one.
They are particulary usefull when you have one normalized input sheet:
http://www.oooforum.org/forum/viewtopic.phtml?p=110806#110806

[RickRandom]

Only help I can offer is with the last point in your original request, for white space.

Put borders round the stuff you want.

Then Tools->Options, expand the + for OpenOffice.org Calc, and de-select Grid lines

[alt7]

Thank you for your answers.

Villeroy, I was going to implement this by making a desktop or a web-based application, but I do not have the time at the moment to plan it completely and I have thought that the particular features I've presented here could be done in Cals using the standard functions or a simple macro until I could make the application someother way.

So if I still want to make it in Calc, is it possible?
Any hints you can give me?

Thanks.

[Villeroy]

Some brainstorming:
For now it looks like you have two sheets for input of data.
IMHO you need one single input table with one single row for each transaction.
Input should be completely independant from a given date or month. Simply append all transactions as they arrive on you desk.
ID: Filled by a macro, identifying the next free transaction-ID eg. by calculating MAX(ID)+1 (a database would care for this)
Doc No: Some ID of the receipt (well, you may use this instead of an ID)
Amount: the amount of the transaction
Date: Accounting date(a database would care for this)
Credit-Account: receiving account ... from some list (a database would care for this)
Debit-Account: issuing account ... from some list (a database would care for this)
You have to prevent missing entries as well as duplicates (a database would care for this)
Some field(s) for remarks
Some calculated fields =Year(Date), =Text(date;"YYYY-MM"), =text(Date;"YYYY-QQ") showing year, month and quarter.
One or two pivot tables could do all the aggregation by accounts, month, year, quarter as shown at http://www.oooforum.org/forum/viewtopic.phtml?p=110806

For calculating conditional sums you may use SUMIF, SUMPRODUCT or some DB*-function like DBSUM
Conditional formatting in order to highlight any missing, duplicate or invalid entries.
Validation by list in order to restrict input to some valid entries (valid accounts, amounts >0, data-validation in spreadsheets is more like an input-help rather than validation)
Most important:
Hide any kind of spreadsheet, dealing with real money, from you tax accountant.