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

Author Message
pete.zhut
Newbie

Joined: 20 Oct 2005
Posts: 1
Location: New Jersey

 Posted: Thu Oct 20, 2005 4:11 am    Post subject: Ledger questions I have a ledger that I am trying to create with OO Calc. I would like to have a formula (or function or macro) that will do the following: When I enter a value in a block it will take that value and give me the sum in another block, but I don't want to have to create functions for each cell. Here is a visual: ---A------------------------B----------------C 1 Starting balance---1000.00----1000.00 2 Check-----------------(-250.00)---750.00 What I want is for the total calculation, the value of C and what ever row is above the newest entry, and the newly entered value to be automatic. I know that I can do this manually with C2=SUM(C1:B2), but is there a way to do this so that when I make a new entry that the total is calculated? Thanks
jrkrideau
Super User

Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

Posted: Thu Oct 20, 2005 5:26 am    Post subject: Re: Ledger questions

 pete.zhut wrote: I have a ledger that I am trying to create with OO Calc. I would like to have a formula (or function or macro) that will do the following: When I enter a value in a block it will take that value and give me the sum in another block, but I don't want to have to create functions for each cell. Here is a visual: ---A------------------------B----------------C 1 Starting balance---1000.00----1000.00 2 Check-----------------(-250.00)---750.00 What I want is for the total calculation, the value of C and what ever row is above the newest entry, and the newly entered value to be automatic. I know that I can do this manually with C2=SUM(C1:B2), but is there a way to do this so that when I make a new entry that the total is calculated? Thanks

At the very simplist just copy cell C2 and paste it into C3. When you enter data in B3 it will take the values from C2 and B3 and put them in C3. The equations are relative in postion.

I would suggest having a look at some of the User's Guide chapters at http://oooauthors.org/en/authors/userguide2/calc/draft_pub/ for some general information .
_________________
jrkrideau
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Thu Oct 20, 2005 4:25 pm    Post subject: Re: Ledger questions

 pete.zhut wrote: ---A------------------------B----------------C 1 Starting balance---1000.00----1000.00 2 Check-----------------(-250.00)---750.00 What I want is for the total calculation, the value of C and what ever row is above the newest entry, and the newly entered value to be automatic. I know that I can do this manually with C2=SUM(C1:B2), but is there a way to do this so that when I make a new entry that the total is calculated?

In C2 you put this: =IF(B2="";"";C1+B2)

Now look at the formula cell and notice a tiny rectangle [handle] in the lower right corner. Grab this with your mouse and drag down th column. This copies the formula for you. Your entries will be in column B and the calculated sum if entered as a positive value, or differecne if entered as a negative value will be reflected in column C. The cells in column C will be blank if there is no entry in their opposing cell in column B.

David.
carl
Super User

Joined: 21 Apr 2003
Posts: 920
Location: Germany

 Posted: Fri Oct 21, 2005 12:02 am    Post subject: I have often used this method but it has a drawback which is that adding a row in the middle of B1:Bn breaks the formula and can (if you dont spot it )lead to a wrong final total. However: In C2 you put this: =IF(B2="";"";\$B\$1:B2) and drag down , this will not happen._________________carl Using OpenOffice.org 2 on XP sp2
cwongcpa
General User

Joined: 16 Jul 2006
Posts: 18

 Posted: Mon Jul 17, 2006 5:17 pm    Post subject: Carl, Do you still need help ? I am also using excel to do with my ledger in my audit firm. If you still need the solution, I could post the formulas and discuss with you. chris
carl
Super User

Joined: 21 Apr 2003
Posts: 920
Location: Germany

 Posted: Mon Jul 17, 2006 9:22 pm    Post subject: Thanks for the thought. I am ok._________________carl Using OpenOffice.org 2 on XP sp2
 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