Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-01-2014, 08:40 AM
bbucher bbucher is offline Calculating values from Drop Down Selections Windows 7 32bit Calculating values from Drop Down Selections Office 2010 32bit
Novice
Calculating values from Drop Down Selections
 
Join Date: Dec 2014
Posts: 6
bbucher is on a distinguished road
Default Calculating values from Drop Down Selections

I am creating a performance evaluation form with different rating criteria (1, 2, 3) in drop down selections.

I am unable to get the formula to recognize the value of the drop down selection and calculate a total (Performance Appraisal Score section - Page 6)

For example, I'm trying to total the values of Dropdowns OC1 through OC7 and get an Erro message of !The Formula Not in Table

Thank you in advance for any assistance!

Bill
Attached Files
File Type: docx PA Template.docx (235.9 KB, 34 views)
Reply With Quote
  #2  
Old 12-01-2014, 10:21 AM
JimP JimP is offline Calculating values from Drop Down Selections Windows 7 32bit Calculating values from Drop Down Selections Office 2010 32bit
Experienced User
 
Join Date: Jun 2010
Location: Virginia Beach, VA
Posts: 742
JimP will become famous soon enough
Default

I am not the one to help you (no experience) but, I did notice a similar thread (scroll down below) that MAY help you at: https://www.msofficeforums.com/word-...g-results.html. The information is about 2 years old but may still be helpful to you.

If not, the resident MVPs/experts can offer you some assistance.
Reply With Quote
  #3  
Old 12-01-2014, 09:02 PM
macropod's Avatar
macropod macropod is offline Calculating values from Drop Down Selections Windows 7 64bit Calculating values from Drop Down Selections Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Your document uses a mix of content controls (for the dropdowns) and formfields (for the checkboxes). You really shouldn't use content controls and formfields in the same document; they don't work well together and can produce erratic document behaviour.

If you restrict yourself to using just formfields, including for the dropdowns, you can do all the calculations without the need for VBA. You do that by checking each formfield's 'calculate on exit' property, ensuring it has a unique internal bookmark name, then referencing those bookmark names in a formula field that does the tallying. The field code to tally the results would look something like:
{={REF Dropdown1}+{REF Dropdown2}+{REF Dropdown3}+{REF Dropdown4}+{REF Dropdown4} \# 0}
where Dropdown1, etc. are the Dropdown's internal bookmark names.

With content controls, however, you will need VBA.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 12-02-2014, 11:48 AM
bbucher bbucher is offline Calculating values from Drop Down Selections Windows 7 32bit Calculating values from Drop Down Selections Office 2010 32bit
Novice
Calculating values from Drop Down Selections
 
Join Date: Dec 2014
Posts: 6
bbucher is on a distinguished road
Default Thank you!

I really appreciate the feedback.

I tried updating the formfield to the 'calculate on exit', however, I don't see that as an option under Developer, Properties.

How would I convert all drop downs to this format?
How do I create unique internal bookmark names for each drop down? I thought I already completed this (OC1, OC2,...OC7) for the organizational competencies section (Section I), yet it doesn't appear to calculate when adding the formula you sent.

Thank you so much. I am so lost with completing this document.
Reply With Quote
  #5  
Old 12-02-2014, 02:25 PM
macropod's Avatar
macropod macropod is offline Calculating values from Drop Down Selections Windows 7 64bit Calculating values from Drop Down Selections Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As I indicated in my previous post, that is a formfield option. Your attachment uses content controls for the dropdowns, so you'd have to replace those with the formfield version.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 12-03-2014, 09:27 AM
Charles Kenyon Charles Kenyon is offline Calculating values from Drop Down Selections Windows 7 64bit Calculating values from Drop Down Selections Office 2010 32bit
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,082
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Quote:
Originally Posted by bbucher View Post
I really appreciate the feedback.

I tried updating the formfield to the 'calculate on exit', however, I don't see that as an option under Developer, Properties.

How would I convert all drop downs to this format?
How do I create unique internal bookmark names for each drop down? I thought I already completed this (OC1, OC2,...OC7) for the organizational competencies section (Section I), yet it doesn't appear to calculate when adding the formula you sent.

Thank you so much. I am so lost with completing this document.
There is no automated way to convert content control dropdowns to legacy formfield dropdowns. I'm sure someone could come up with a vba method for doing this but it would not be a minor undertaking. If you have hundreds of them, you might want to pay someone (else) to do this. I would not attempt it and expect it would not be cheap. Otherwise, you will need to create new (legacy) formfield dropdowns and delete the existing (content control) ones.

Greg Maxey's Add-In for legacy form fields can be useful. Add Classic FormField Controls to Ribbon
Reply With Quote
  #7  
Old 12-03-2014, 02:51 PM
bbucher bbucher is offline Calculating values from Drop Down Selections Windows 7 32bit Calculating values from Drop Down Selections Office 2010 32bit
Novice
Calculating values from Drop Down Selections
 
Join Date: Dec 2014
Posts: 6
bbucher is on a distinguished road
Default Error with the formula?

Thank you. I deleted the content control boxes and inserted form field drop down boxes, renamed each box and inserted the formula previously provided but I'm receiving the error "!Syntax Error, {" for each calculation. Did I copy the formula incorrectly?

Also, I am not able to select a drop down choice in the form fields. What do I need to do to allow the user to edit the document and be able to select an option from the form fields?

Attached is the updated document for reference.
Attached Files
File Type: dotx PA Template.dotx (233.9 KB, 30 views)
Reply With Quote
  #8  
Old 12-03-2014, 03:03 PM
macropod's Avatar
macropod macropod is offline Calculating values from Drop Down Selections Windows 7 64bit Calculating values from Drop Down Selections Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The field brace pairs (i.e. '{ }') for the example I posted are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this forum. Nor is it practicable to add them via any of the standard Word dialogues.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 12-04-2014, 12:20 PM
bbucher bbucher is offline Calculating values from Drop Down Selections Windows 7 32bit Calculating values from Drop Down Selections Office 2010 32bit
Novice
Calculating values from Drop Down Selections
 
Join Date: Dec 2014
Posts: 6
bbucher is on a distinguished road
Default Still having issues with the formula

Thank you for the feedback.

I entered the formula using Ctrl F9 into the document but can't seem to get the calculation to work per the attachment.
Attached Files
File Type: docx PA Draft.docx (231.9 KB, 13 views)
Reply With Quote
  #10  
Old 12-04-2014, 12:45 PM
macropod's Avatar
macropod macropod is offline Calculating values from Drop Down Selections Windows 7 64bit Calculating values from Drop Down Selections Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The braces around each of your {REF EJC1}, {REF EJC2}, {REF EJC3}, etc. in your Score formulae are NOT field braces - they're just plain text braces. As previously advised, you have to create these (as pairs) via Ctrl-F9.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 12-08-2014, 08:00 PM
bbucher bbucher is offline Calculating values from Drop Down Selections Windows 7 32bit Calculating values from Drop Down Selections Office 2010 32bit
Novice
Calculating values from Drop Down Selections
 
Join Date: Dec 2014
Posts: 6
bbucher is on a distinguished road
Default Calculation Help

Thank you. I'm sorry, but I still don't understand exactly how it needs to be formatted. Could you either walk me through each step or complete a calculation in the document that I could use as an example?

Thank you in advance.
Reply With Quote
  #12  
Old 12-08-2014, 08:28 PM
macropod's Avatar
macropod macropod is offline Calculating values from Drop Down Selections Windows 7 64bit Calculating values from Drop Down Selections Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

See attached - I've added fields for the aggregates, too.

I note that your form still has some drop-down content controls for the Development Plan. As previously advised:
Quote:
You really shouldn't use content controls and formfields in the same document; they don't work well together and can produce erratic document behaviour.
Attached Files
File Type: docx PA Draft.docx (232.4 KB, 128 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 12-11-2014, 08:50 AM
bbucher bbucher is offline Calculating values from Drop Down Selections Windows 7 32bit Calculating values from Drop Down Selections Office 2010 32bit
Novice
Calculating values from Drop Down Selections
 
Join Date: Dec 2014
Posts: 6
bbucher is on a distinguished road
Default Editing

Thank you very much!

I will amend the content controls for development plans.

How can I save the document where users can edit information? It appears the only actions possible are using the drop down selections?
Reply With Quote
  #14  
Old 12-11-2014, 12:47 PM
macropod's Avatar
macropod macropod is offline Calculating values from Drop Down Selections Windows 7 64bit Calculating values from Drop Down Selections Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

In a document with forms protection, the only edits allowed are in formfields or in unprotected Sections of the document. You could use text formfields for inputting the Position, Employee Name, Date & Reviewer data (though I'd move these out of the textbox you currently have them in) and for the comments/explanations. For Sections V, VI & VIII, you could insert Section breaks either side, then leave that Section unprotected when applying the forms protection. That way, Sections V, VI & VIII could have free text input. You could do the same for the portions titled 'Behaviors, Work Habits, and Attitude issues contributing to rating score above', but that would put visible breaks in the tables.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 04-09-2017, 07:13 PM
climber43 climber43 is offline Calculating values from Drop Down Selections Mac OS X Calculating values from Drop Down Selections Office 2016
Novice
 
Join Date: Apr 2017
Posts: 1
climber43 is on a distinguished road
Default

Hey there macropod!
I'm still looking for a way to calculate an overall score from dropdown (or use form fields). You sent me here referencing a document. All I find is a document that's somebody else's evaluation form. That doesn't help. Is there a document on how to create form fields? I really want to be done with this. Thanks in advance for providing a short, sweet, and straightforward answer and not sending me somewhere else.
Kim
climber43
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating values from Drop Down Selections Contact details entered based on multiple drop down selections ntropey87 Word VBA 4 04-20-2013 11:19 AM
Calculating values from Drop Down Selections Drop Down list with calculations and values winter4400 Word 7 01-16-2013 04:55 AM
add values to dropdown selections and calculate guyhs Word 7 10-24-2012 05:11 PM
Calculating values from Drop Down Selections Assigning Values to content control checkboxes and calculating results creative cathy Word Tables 13 10-07-2012 08:52 PM
calculate average depending on drop-down values virencm Word 0 08-06-2010 06:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft