OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

I need a few answers

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
alt7
General User
General User


Joined: 02 Feb 2006
Posts: 5

PostPosted: Fri Feb 03, 2006 3:54 pm    Post subject: I need a few answers Reply with quote

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.
Back to top
View user's profile Send private message
alt7
General User
General User


Joined: 02 Feb 2006
Posts: 5

PostPosted: Sat Feb 04, 2006 4:27 am    Post subject: Reply with quote

Is this something I can do with Pivot Tables?

Is there a tutorial on this for Open Office?

Thanks.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Feb 05, 2006 2:27 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Feb 05, 2006 3:24 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
alt7
General User
General User


Joined: 02 Feb 2006
Posts: 5

PostPosted: Sun Feb 05, 2006 1:15 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Feb 05, 2006 4:39 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group