At 17:38 13/07/2017 -0700, Girvin Herr wrote:
On 07/13/2017 04:58 PM, Brian Barker wrote:
At 15:11 13/07/2017 -0700, Girvin Herr wrote:
I have a Writer document with about 50 rows in a table. At the
bottom of the table, I want to sum the above column of cells in
each row (a total). Currently, the only way to add a new row or
rows to this sum is to add the cell reference(s) (i.e. |<J47>) to
the long string of cell references to sum. Is this the only way,
other than dragging from the first cell to the last cell?
Dragging is surely not such a bind? If you have the formula =sum
<B1:B50> and you add a fifty-first row of data,
o Put the cursor into the cell containing the total.
o Press F2 to display the Input Line at the top (as you see in
using a spreadsheet). The Formula Text window still displays =sum <B1:B50> .
o Drag across the new range. The Formula Text (also shown in the
total cell) changes to =sum <B1:B51> .
Actually, no. What it changes to is =sum(<B1>|<B2>|...|<B51>).
Er, not in my OpenOffice 4.1.3, it doesn't. I did test this
thoroughly before writing. I don't see how you could get anything
like this unless you had started with the cells referred to
separately and not as a range. And where did those parentheses come
from? There were none in my example. I strongly suspect that these
are what are causing you problems. (I've now seen how this has
happened: read on.)
If I then drag a new set of cells, the new range is appended to the
long string of cell references. That means that I must delete the
original string before dragging the new set. I just tried some
experiments with dragging and it is a mess - very error prone.
This is all down to your superfluous parentheses, I think.
I did try your example of =sum(<B1:B50>) ...
No: that was not my example; you have added gratuitous parentheses again.
... and it produced the ** Expression is faulty ** message.
It doesn't complain for me - despite those parentheses.
However, when I selected the Sum icon again ...
Aha! I wasn't using the Sum icon, and it does indeed lead to a
formula listing the cells in the range individually - instead of as a
range. I don't see why this should be. This indeed makes subsequent
dragging problematic. And I now see where your parentheses come from.
Instead, try this:
o Put the cursor into the cell to contain the total.
o Press F2: you will see an equals sign in the Formula Text window.
o Add just the three letters "sum" - nothing more - to read "=sum".
(Er, certainly no parentheses!)
o Now just drag over the range of cells.
You should now see just =sum<B1:B50> , and the dragging I proposed
before should now work for modifications.
1. Insert your new row of data somewhere else - within the existing
range, that is. (You are given the option to insert new rows Before
instead of After, which may help.) If the order of your data in the
table rows is not significant, this may suffice.
I tried this and it worked. However, I got the error message again
and had to do the clicking on the sum icon and the green check icon
again, then it fixed itself. The good news is that this option is
closest to what I would like to see. I can insert as many rows as I
need and still get the sum to follow it without manually editing
that long string of cell references. It is a reasonable option to me.
I'd avoid the Sum icon.
2. Alternatively, if you want to maintain some order and insert
your new data, say, at the end of the existing material, here is a workaround:
o Insert your new row *before* the last row of the existing data -
so that it becomes the new fiftieth row of what are now fifty-one
rows. (The total cell's formula has been automatically modified to
=sum <B1:B51> ).
o Select the entire fifty-first row (your original fiftieth row).
o Cut this data and paste it into the new fiftieth row.
o Add your new data to the now empty new fifty-first row.
No need to touch the formula.
Yes, I have done this in Calc. Until you mentioned it, I was not
aware it can also be done in Writer tables.
Yes, Brian, it has helped.
Good-oh!
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
