Calculations in Word 2003 Table

[Guest]

Tried asking this in another newsgroup but they suggested that I come to a
Word Newsgroup to find my answer faster...

Been asked by a user who is looking to create a table in Word to find a way
to get some of the cells to become (like Excel) calculatable (if there is
such a word).

I first thought that copying the cells from a worksheet (with formulae in)
and do a Edit > Paste Special > MS Excel worksheet object...

It looked like it worked ok, but that was not what the user wanted since
they only wanted one cell to input a figure in, another one to input a
percentage and a third (all in seperate cells in the Word table) which would
display the outcome of the formula (=A1*A2%)...

Also tried a Nested Table, but that is just a copy and paste of the single
cell with no formula or border and so doesn't change when the other cells are
altered.

Are there any other methods? Or am I asking the impossible?

 

[Graham Mayor]

Put the figure in cell A1
Put the percentage in cell A2 (entered as e.g. 10%)
Put a formula field in A3
{ =A1*A2 }
Update the field (CTRA+A then F9) or use a macro - see example at
http://www.gmayor.com/installing_macro.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Guest]

Ok think I have that, but it can't seem to link with the spreadsheet with the
info on...

I'm trying to have this so that after creating the document it cuts out
Excel altogether...

 

[Graham Mayor]

I had assumed you wanted to do all of it in Word, in which case where does
Excel still come in?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Guest]

I do... The bit that i'm not understanding is after updating a field it comes
up in bold lettering...

'Error! Bookmark not defined.'

Which is why i'm trying to link in Excel... Or is this supposed to happen
and I just need to do something else in Word?

 

[Graham Mayor]

Let's start with a simple table created in Word
Type a number in cell A1 (that'sthe first cell in Word)
in the cell beneath it (A2) type 10%
in the cell beneath that (A3) type CTRL+F9
which will give you {}
between the brackets type =A1*A2
thus {=A1*A2}
Toggle the display ALT+F9 then F9 to update. A3 should show a figure 10% of
the figure in A1
Where is what you are doing differing from this?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Guest]

Ahhh I see now! I was actually putting the { } symbols with the data and
percentage in the other cells aswell as the one to display the total...
Obviously i've not been shown anything like this before to know that until
now...

Also it didn't make sense with me not having the cell references correct
(equivilant of cells D3 - D5)!

Thanks greatly, was about to give up on this and say it couldn't be done!

 

[Graham Mayor]

The only minor issue is that the calculated field will not update
automatically (as it would in Excel) and so you have to force an update if
you make any changes to the number/percentage.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Guest]

Is there any way at all of making this an instant update? Or any other
alternatives to achieving the same task in question?

 

[Graham Mayor]

If you use form fields to collect the numbers and a calculated field (this
time it would have to catch the bookmarks that are the form field names
rather than the cell locations - e.g {={REF Text1} * {REF Text2}} then
provided the field is not in a header/footer and you have checked the
calculate on exit text boxes of the fields that contribute to the
calculation it will update automatically as you tab out of the form fields.

The disadvantage with this is that a document locked for forms severely
curtails what else you can do with that document. Adding the macro code from
http://www.gmayor.com/installing_macro.htm to update the field to a toolbar
button in the document's template would be a better solution if you want to
retain editing capabilities throughout the document.


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>