[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
alt7
General User

Joined: 02 Feb 2006
Posts: 5

 Posted: Fri Feb 03, 2006 3:54 pm    Post subject: I need a few answers 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
General User

Joined: 02 Feb 2006
Posts: 5

 Posted: Sat Feb 04, 2006 4:27 am    Post subject: Is this something I can do with Pivot Tables? Is there a tutorial on this for Open Office? Thanks.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Sun Feb 05, 2006 2:27 am    Post subject: 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
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Sun Feb 05, 2006 3:24 am    Post subject: 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
General User

Joined: 02 Feb 2006
Posts: 5

 Posted: Sun Feb 05, 2006 1:15 pm    Post subject: 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
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Sun Feb 05, 2006 4:39 pm    Post subject: 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.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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