Help with If statements in a Word Table using Form fields

[Carol]

I have a word table that I am using with locked fields. In the table there
is a column of numbers (4 rows) that the person types in a number from 0-4
depending on some criteria. I have totalled these numbers at the bottom and
the sum field is bookmarked as 'Total'. The maximum number can be 16
depending on what they selected above. Under the table I have another row
that depending on what the 'Total' value is it will put an 'X' in the box
that depicts what level they are. For example... if 'Total' <4 it should put
an 'X' in the first box. If 'total' >=4 and <7 it puts an 'X' in the second
box. Here's the IF statement I've used for the second box.

{ =if(Total>=4 and <7,X, ) }

Nothing seems to work no matter what I put in here. Any help would be
GREATLY appreciated.

Carol

 

[Suzanne S. Barnhill]

You can't put two conditions in the same IF field; this requires nested IF
fields. See the examples for "Specify multiple conditions" in the Help topic
"Examples of IF fields."

 

[Carol]

Ok fair enough but it doesn't even work where I only have one condition.
This is another expression I have: { =if(Total>7,X, ) }

Carol

 

[Suzanne S. Barnhill]

Well, first of all, there is no =IF field; it's just IF. If the Total field
is a form field, and you want the IF field to result in X when Total is
greater than 7, then you need { IF { REF Total } > 7 "X" }

 

[Carol]

Hi Suzanne, that didn't work either. I think I'm going to have to rethink my
form and try and do it some other way... or transfer it over to Excel where I
know how to do it but it won't look as nice.

Thanks for trying to help.

Carol

 

[Suzanne S. Barnhill]

Sometimes it helps to put the bookmark name in quotes. I assume that the
calculated field actually has the bookmark "Total"?

 

[Peter Jamieson]

1. Does your "Total" bookmark bookmark the field (in which case you have a
chance of making something work) or the entire cell (in which case you need
to bookmark the field instead)? (One way is to use the fields { SET Total
{ =sum(above) } }{ REF Total } in column 1, row 5)

2. Are all the {} the special field braces you can insert using ctrl-F9?

3. Assuming your addition row is row 6 in the same table, you can use the
following in A6:

{ IF { REF Total } < 4 "X" "" }

and the following in B6:

{ IF { REF Total } >= 4 "{ IF { REF Total } < 7 "X" "" }" "" }

and so on.

4. As an alternative to using the bookmark Total, you could use e.g.

{ IF { =sum(A5:A5) } < 4 "X" "" }

and so on. Just bear in mind that Word's "= field" capabilities are much
more limited than Excel's.

5. As an alternative to using nested IFs, you can use a calculation and a
numeric formatting switch to provide the result, e.g.

{ =3.9-{ REF Total } \#"'X';" }

(if you want to go that route, I leave you to work out how to do >=4 and < 7
etc.: personally I find it simpler and clearer to spell out the conditions
using IF fields even though the syntax is not very clear. Or you could do
e.g.

{ SET cond1 { =3.9-{ REF Total } \#"'X';" } }{ REF cond1 }

then reuse the result using something like

{ SET cond2 { IF { REF cond1 } = "X" "" "{ = 6.9-{ REF Total }
\#"'X';" }" }{ REF cond2 }

and so on.

 

[Suzanne S. Barnhill]

I was operating on the assumption that this was a calculated form field; if
not, then your further instruction is certainly to the point. Bookmarks in
form fields are so much less troublesome than manually applied bookmarks.

 

[Peter Jamieson]

Yes, I wasn't sure which approach was being used either. Time may tell!

 

[Carol]

Hi again, sorry I didn't make myself clear. I am using form fields and yes
Suzanne is correct, the 'Total' bookmark is a calculated form field.

I am not very up on it when it comes to working with calculations in form
fields... I know that's pretty obvious isn't it!

I tried putting the quotes around the field name and that didn't work. I'm
running out of time to deliver this template. I think I'll drop the idea of
having it automated and I'll provide a reference table on the form to show
that depending on what the 'total' value is they can select from the table
what category they need to work in.

Thanks for all your help guys.

Cheers.

Carol

 

[Suzanne S. Barnhill]

One gotcha about calculated fields: you have to enable "Calculate on exit"
in every form field that contributes to the calculation. Also, don't forget
that the IF field may not update automatically; you may need to click in it
and press F9 to update it (though sometimes forms don't let you do this, so
that requires another macro of its own); REF fields *do* update
automatically, however, provided "Calculate on exit" is checked in the
referenced form field.

 

[Carol]

Thanks Suzanne. Yes I had 'calculated on exit' ticked in every box. I
didn't realise that F9 updated the fields. I must admit that this one got
the better of me. I may have a go at trying it on the side but I need to get
these forms delivered today. I provided a reference table for the user to
see what category they are working in depending on the value of the 'total'
field. I'll make sure that the instruction notes for the template clearly
state how to use the reference table.

Thanks again.

Carol