Hi there!

I use Protected Word forms for surveys in my business.

I bookmark all the answers (which are often YES/NO dropdowns, or
numberic fields), and we paste the bookmarks into Excel for data
analysis.

Everything is working well, but now I would like to get a little
fancier and increase the sophistication of the Word forms.

Therefore, I need some help building a few equations in my documents.

The answer to this question could probably answered by a knowledgeable
person in 5 minutes.  Or maybe what I ask can simply not be done.

Here?s what I need:

1 ? Using nested IF statements.  Our forms have a bookmarked field
entitled TOTALSCORE.  We also have a field called GRADE, which can be
A, B, or C, depending on the Total Score.

Right now, we use a manual drop down to select A, B, C, depending if
Total Score is 81% - 100%, 70% - 80%, less than 80%, respectively.  In
Excel, this is easy (=IF(O1659<>"",IF(O1659>=0.81,"A",IF(O1659>=0.7,"B","C")),"")).
 But I can?t do it in Word.  Anyone?


2 ? Right now we have people answer questions numerically in a
vertical column in a table, like this:

4
5
0
2

And at the bottom, I easily obtain a total by using the =Sum(Above)
formula.  Very simple.

HOWEVER?

I would LOVE to use drop down boxes so they could select 0 or 5, 0 or
2, 0 or 4, etc., for each question.  (I am a big drop down fan from a
usability standpoint)

Unfortunately, I can?t get it to work.  
When I try to add up a bunch of drop downs, it doesn?t work.  It stays a zero.
Could it be that it considers them as text labels and not numbers?

Any insight would be appreciated!


3 ? I was thinking of having the user drop down Yes or No from a box,
and then in another cell, the score will be filled in automatically.

I just did a test, calling one dropdown DROPDOWN1, and then putting
this in a formula:

IF dropdown1 = "Yes" "Very Good" "Very Bad"

No luck.
It doesn?t change from Very Bad.

So these are my Word issues.

To repeat:
==========
1 ? Simple conditional IF statement
2 ? Adding up dropdowns
3 ? Using an IF statement based on another bookmark

If you can answer two of these things, but tell me the third cannot be
done, of course, I?ll consider this answered.

Thank you very much for your help!

Mike

Mike: I've written a book on Microsoft Word, so I figure I'm qualified
to answer your question.

But my response keeps coming back to this:

It will require VBA to accomplish your goal smoothly.
It will not require any VBA to create your application in Excel.

What is your reason for wanting to keep it in Word?
Anything against moving to Excel?
Are you aware that you can make an Excel application LOOK very much
like a Word Processor by, for instance, hiding sheet tabs, gridlines,
and column and row headings? You can even make it look like a wizard.

I have not done too much fancy stuff, but I hope you get the idea:
www.vbaexpress.com/EE/sample.zip

I hope it helps.

Hi there!

Thank you very much for your reply!

I totally agree with you -- I'm a big fan of Excel.

The problem is our clients' preference.

These reports get sent throughout all levels of our retail clients.

They all want Word.  

One thing about Excel -- although it's wonderful for many things, when
it comes to printing and editing text, it's just not as cool as word.

A good 'ol Word document is simply way better for its portability, its
WYSIWYG interface, it's spell check, and so forth.

Word is just the way to go.

We have some new clients who have given us pre-existing forms in
Excel, and each one is worse than the next.  Excel is INCREDIBLE for
numbers and stats and graphs and charts... but for our needs, which
are primarily text-based forms...   Word is definitely the way to
go...!

MG

I can likely get this coded for you, but will need a sample file with
which to work. Can you upload one? And/or answer these questions:

1 & 2. How is the numeric score calculated? By adding up dropdowns
values or otherwise? Can the numeric score show, but the letter grade
be calculated anyway? or is it your intent to hide the numeric score?

Also...has anyone considered using a userform and THEN producing a
document based on the input? You'll find an example here:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=184

Hi again!

Thanks for the quick reply.

Your userform is quite neat!  For our needs, though, every single
client (except for one) wants their forms in Word.  It may not be the
optimal thing, but heck -- it works.

To answer your question...
Right now, the users fill in one- or two-character length numeric
fields with their number of choice.

For most questions, we allow 0 or 5, 0 or 10, 0 or 2, etc. 
=sum(above) works like a charm.

The reason why I want to investigate drop downs is because even if we
state "Pick 0 or 10", it never fails... some users type in 7 or 6
or...!

So that is why I wish dropdowns would work.

And yes, the numeric value is definitely shown in its own cell, and is
bookmarked as well. "TOTAL_SCORE".  The grade is a separate cell
altogether.

I have created a simple Word document with two tables that illustrates
the three things I am trying to accomplish.  Simple generic stuff.

Can you recommend a place where I could upload this?

Thanks!!