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>). 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.
I did try you're example of =sum(<B1:B50>) and it produced the **
Expression is faulty ** message. However, when I selected the Sum icon
again and used the green check to okay it without changing anything, the
error went away and the correct sum was displayed again. I didn't really
do anything to the formula. Also, the <B1:B50> was changed to the
<B1>|<B2>|...|<B51> again. So it did not stick.
o Either click the green "Apply" tick mark in the Input Line, or
simply press Enter.
I would like to be able to add rows to this table as needed and have
an easy way to add the new cells to the sum. A way of specifying a
range of cells is perfect. As I now do it, the more rows I need to
add, the more tedious and error-prone this process becomes.
This is problematic only if you choose or need to add rows *outside*
the existing range of rows to be summed - before the first row or
after the last row in the existing summed range, that is. This
suggests two further possibilities:
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.
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.
I trust this helps.
Brian Barker
Yes, Brian, it has helped.
Thanks.
Girvin
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
