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

Author Message
wlaw
General User

Joined: 14 Sep 2006
Posts: 48
Location: Hong Kong

 Posted: Mon Oct 09, 2006 8:26 am    Post subject: How to do this calculation ? I would like to do the following. In sheet 1, A1 is the sum of all entries in sheet 2, colum 1, (say, A1 to A10), however, I will add one more items periodically, say, A11 one month later, A12 six months later. How can I make it add to the last item in sheet 2 column 1 automatically ? Thank you !_________________I hate MsOffice becasue it's dear. I like OpenOffice becasue it is FREE.
General User

Joined: 09 Oct 2006
Posts: 5
Location: Sierra Vista, AZ

 Posted: Mon Oct 09, 2006 9:57 am    Post subject: In Excel, you can do a SUM function on an entire column. The formula looks like this: =SUM(Sheet2!A:A) Just substitute the word Sheet for the actual name of the worksheet. I'm not sure if the syntax is precisely correct in Calc, though, so you might want to play around with it._________________-Schadeboy "Perhaps, if I am very lucky, the feeble efforts of my lifetime will someday be noticed, and maybe, in some small way, they will be acknowledged as the greatest works of genius ever created by Man." -- Jack Handy
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Mon Oct 09, 2006 2:35 pm    Post subject:

 Schadeboy wrote: In Excel, you can do a SUM function on an entire column. The formula looks like this: =SUM(Sheet2!A:A) Just substitute the word Sheet for the actual name of the worksheet. I'm not sure if the syntax is precisely correct in Calc, though, so you might want to play around with it.

Nope, the entire column has to be addressed as Sheet2.A1:A65536
Calc's sheet-spearator is "."(dot). "!" is the intersect-operator.
The rules of relative/absolute addressing apply to sheets as well, so you may want \$Sheet2.A1:A65536 or even \$Sheet2.\$A\$1:\$A\$65536
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
wlaw
General User

Joined: 14 Sep 2006
Posts: 48
Location: Hong Kong

 Posted: Mon Oct 09, 2006 4:02 pm    Post subject: Thank you for your information, I've tried but it didn't work ! Why ? Can you try it in your Cal to see if it works or not ? Thank you !_________________I hate MsOffice becasue it's dear. I like OpenOffice becasue it is FREE.
Sliderule
Super User

Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun

Posted: Mon Oct 09, 2006 6:27 pm    Post subject:

wlaw:

Yes, it does work for me . . . as described by Villeroy.

To be specific, . . . if you use the formula:
 Code: =SUM(\$Sheet2.\$A\$1:\$A\$65536)

So, wherever you place the formula . . . it will SUM all the values in Sheet2, from the range, A1 through and including A65536.

I hope this helps, please be sure to let me / us know.

Sliderule
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Tue Oct 10, 2006 7:55 am    Post subject:

To be even more specific:
In OOo1.x we have only 32000 rows and should use \$Sheet2.\$A\$1:\$A\$32000

 Quote: Thank you for your information, I've tried but it didn't work ! Why ? Can you try it in your Cal to see if it works or not ?

This statement is very unspecific.
Does the program crash?
Do you get an error-value? Which one?
Do you get an unexpected result?

Let the program do the input:
Select Sheet2.A1 and hit Ctrl+Shift+down until you reach the bottom.
The formula bar shows =SUM(Sheet2.A1:A65536
Now you may hit Shift+F4 in order to convert this relative reference to =SUM(\$Sheet2.\$A\$1:\$A\$65536
Hit enter (a single missing brace at the end is closed automtically
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
General User

Joined: 09 Oct 2006
Posts: 5
Location: Sierra Vista, AZ

 Posted: Tue Oct 10, 2006 8:46 am    Post subject: Yeah, I didn't expect the syntax to be exact. But the concept is the same, anyway. This assumes that you won't ever have to put anything else in that same column, too._________________-Schadeboy "Perhaps, if I am very lucky, the feeble efforts of my lifetime will someday be noticed, and maybe, in some small way, they will be acknowledged as the greatest works of genius ever created by Man." -- Jack Handy
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Tue Oct 10, 2006 9:06 am    Post subject:

 Schadeboy wrote: Yeah, I didn't expect the syntax to be exact. But the concept is the same, anyway. This assumes that you won't ever have to put anything else in that same column, too.

Right, you should not do that anyway.
But consider this one:
Having a list with headers in first row and consecutive contents in B2:Bxxx, you can sum up column A by finding the first empty cell in B:
=SUM(OFFSET(\$A\$1;1;0;MATCH("";\$B\$2:\$B\$65536;0);1))
=OFFSET(\$A\$1;1;0) gives cell A2
resized by rows: MATCH("";\$B\$2:\$B\$65536;0), which is 99 if first empty cell is B100.
See help on OFFSET() and MATCH()
=SUM(OFFSET(\$A\$1;0;0;MATCH("";\$B\$1:\$B\$65536;0);1))
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
 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