copying formulas into other cells

I am generating a spreadsheet that has formulas in each cell. Each cells formula has to ref. three (3) other cells, (1) of which is the same for all, and the other (2) ref. the uppermost and furthest left cells in line with the cell to be calculated. I do not want to type the formula in every cell. I know MS excel changes the formulas automatically when you copy into another cell. How can this be done in Word?

This was originally posted by macropod, a frequent poster to some of these
groups:

Quote

Say you need a formula on every row to multiply the contents of ColumnA by
the contents of ColumnB, then add the contents of ColumnC, and your formula
starts on Row1 of the table. To do that you could use a compound field like:

{QUOTE
{Set CellA "a{={SEQ RowNr}/2}"}
{Set CellB "b{={SEQ RowNr \c}/2}"}
{Set CellC "c{={SEQ RowNr \c}/2}"}
{={CellA}*{CellB}+{CellC} \# 0;-0}
}
where the braces '{}' are entered in pairs via Ctrl-F9. I've laid the field
out this way for readability - you can dispense with the internal CRs.

This field works by creating a sequence number of each row and incorporating
that plus the required column letters into bookmarks (CellA, CellB, CellC)
for those rows. These then become the cell addresses referenced in the
formula. You'll notice that the SEQ field has a the \c switch for the CellB,
CellC references, but not for the CellA reference. This is to stop multiple
SEQ references on the same row changing the SEQ No. (and hence the source
row number). You'll also notice that each bookmark includes a '/2' to divide
the SEQ No by 2. That's needed because of a flaw in the way Word updates SEQ
fields when used directly in a cell reference.

If your data doesn't start on the first row in the table, you need to add an
offset to the formula for each row before the first data row. So, if your
data starts on the second row, you'd put +1 after each '/2' expression (i.e.
{SEQ RowNr}/2+1, etc). If the data starts on the third row, you use +2, and
so on. Use the same technique to offset the cell referencing by a
predetermined number of rows, using -ve values to refer to rows above, and
+ve values to refer to rows below.

Relative referencing does not work for columns.

Cheers

Unquote

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
"Gslack" wrote in message
news
I am generating a spreadsheet that has formulas in each cell. Each cells
formula has to ref. three (3) other cells, (1) of which is the same for all,
and the other (2) ref. the uppermost and furthest left cells in line with
the cell to be calculated. I do not want to type the formula in every cell.
I know MS excel changes the formulas automatically when you copy into
another cell. How can this be done in Word?

To see how to create a formula in Word to do relative referencing so
that the formula doesn't need to be re-typed on each row, see:
http://www.wopr.com/cgi-bin/w3t/show...&Number=365442
(url all one line)

Can't be done with columns, though.

Cheers


---
Message posted from http://www.ExcelForum.com/