Help! Form fields in table and calculations!

  • Thread starter Thread starter R.P.McMurphy
  • Start date Start date
R

R.P.McMurphy

Hi all, can someone change this formula-

=(SUM(C9,C17,C25,J9,J17,J25)-6*39+ABS(C9-39)+ABS(C17-39)+ABS(C25-39)+ABS(J9-39)+ABS(J17-39)+ABS(J25-39))/2

to read from the cells in table1?

The form has two tables with form fields in, the first is the input table,
the second is the output or results table. The above works well in a cell
in the input table, but will not work in the output table, it gives strange
results!

Many Thanks!

Steve
 
From the Help file:

To reference cells in another table, or to reference a cell from outside a
table, identify the table with a bookmark (bookmark: A location or selection
of text in a file that you name for reference purposes. Bookmarks identify a
location within your file that you can later refer or link to.). For
example, the field { =average(Table2 b:b) } averages column B in the table
marked by the bookmark Table2.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
I have book marked the table by clicking on the table anchor to highlight
the table and clicking form field options button on the forms toolbar and
then typing 'table1' in the bookmark box. I then, in the formula mentioned,
added table1 before every cell reference. but its still not working for
some reason. am I doing this right?

cheers

Steve
 
No, you need to use the Bookmarks item on the Insert menu to bookmark the
whole table.

For what you have done, you must have some formfields in the table and the
bookmark that you have created will only include the first of those
formfields.

When you insert the bookmark via the Insert menu and you turn on the display
of Bookmarks under Tools>Options>View, you will see the [ marking the
beginning of the bookmark as the first item in the first cell of the table
and the ] marking the end of the bookmark will be outside the table to the
right of the last cell in the last row.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
 
Hi Doug, I have found another way of doing the same thing, leave the
calculation in a unused cell in table1, then set the cell in table2 where I
require the output of the calculation to just repeat what has been the
result of the calculation. I have used

=overtime1

but if overtime1 is 16

then result is coming up as 1616...why?

Steve

Doug Robbins - Word MVP said:
No, you need to use the Bookmarks item on the Insert menu to bookmark the
whole table.

For what you have done, you must have some formfields in the table and the
bookmark that you have created will only include the first of those
formfields.

When you insert the bookmark via the Insert menu and you turn on the
display of Bookmarks under Tools>Options>View, you will see the [ marking
the beginning of the bookmark as the first item in the first cell of the
table and the ] marking the end of the bookmark will be outside the table
to the right of the last cell in the last row.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

R.P.McMurphy said:
I have book marked the table by clicking on the table anchor to highlight
the table and clicking form field options button on the forms toolbar and
then typing 'table1' in the bookmark box. I then, in the formula
mentioned, added table1 before every cell reference. but its still not
working for some reason. am I doing this right?

cheers

Steve
Click to expand...
Click to expand...
 
Sounds like you are doing a calculation where one of the inputs to the
calculation is itself the result of some other calculation. Word cannot
handle that.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

R.P.McMurphy said:
Hi Doug, I have found another way of doing the same thing, leave the
calculation in a unused cell in table1, then set the cell in table2 where
I require the output of the calculation to just repeat what has been the
result of the calculation. I have used

=overtime1

but if overtime1 is 16

then result is coming up as 1616...why?

Steve

Doug Robbins - Word MVP said:
No, you need to use the Bookmarks item on the Insert menu to bookmark the
whole table.

For what you have done, you must have some formfields in the table and
the bookmark that you have created will only include the first of those
formfields.

When you insert the bookmark via the Insert menu and you turn on the
display of Bookmarks under Tools>Options>View, you will see the [ marking
the beginning of the bookmark as the first item in the first cell of the
table and the ] marking the end of the bookmark will be outside the table
to the right of the last cell in the last row.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

R.P.McMurphy said:
I have book marked the table by clicking on the table anchor to highlight
the table and clicking form field options button on the forms toolbar and
then typing 'table1' in the bookmark box. I then, in the formula
mentioned, added table1 before every cell reference. but its still not
working for some reason. am I doing this right?

cheers

Steve

From the Help file:

To reference cells in another table, or to reference a cell from
outside a table, identify the table with a bookmark (bookmark: A
location or selection of text in a file that you name for reference
purposes. Bookmarks identify a location within your file that you can
later refer or link to.). For example, the field { =average(Table2
b:b) } averages column B in the table marked by the bookmark Table2.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Hi all, can someone change this formula-

=(SUM(C9,C17,C25,J9,J17,J25)-6*39+ABS(C9-39)+ABS(C17-39)+ABS(C25-39)+ABS(J9-39)+ABS(J17-39)+ABS(J25-39))/2

to read from the cells in table1?

The form has two tables with form fields in, the first is the input
table, the second is the output or results table. The above works
well in a cell in the input table, but will not work in the output
table, it gives strange results!

Many Thanks!

Steve
Click to expand...
Click to expand...
Click to expand...
 
Hi Steve,

I think there's something wrong with your formula - as posted, it can only
ever return 0.

As for the external referencing, some functions, including ABS, don't work.
However, you can get around this with:
{=(SUM(Tbl1 C9, Tbl1 C17, Tbl1 C25, Tbl1 J9, Tbl1 J17, Tbl1
J25) -6*39+ABS(SUM(Tbl1 C9)-39)+ABS(SUM(Tbl1 C17)-39)+ABS(SUM(Tbl1
C25)-39)+ABS(SUM(Tbl1 J9)-39)+ABS(SUM(Tbl1 J17)-39)+ABS(SUM(Tbl1
J25)-39))/2}
where Tbl1 is the bookmark name given to the reference table.

One of your later posts mentions the possibility of leaving the calculation
in a unused cell in the reference table, you only need to refer to that
cell. For example, if the formula appears in A30, you could use:
{=SUM(Tbl1 A30)}
or, if you bookmark just the formula (NOT the cell it's in), you could use:
{=BkMrk}
where 'BkMrk' is the name of the formula's bookmark.

Check out my Word Field Maths 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=365442
for more info.

Cheers
 
Back
Top