| View previous topic :: View next topic |
| 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. |
|
| Back to top |
|
 |
Schadeboy
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2473
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 |
|
| Back to top |
|
|
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:
Start your formula =SUM(
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 |
|
| Back to top |
|
|
Schadeboy
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 |
|
| Back to top |
|
|
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()
Without header row:
=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 |
|
| Back to top |
|
|
|
