Hello,



I have a table in Word 2010 where I need to add tax to give me a total dollar amount. I have a check box called 'Tax Exempt?' in cell B12, and a formula in D12 which shows the tax in dollar amounts, =Product(D11,.0835), number format of $#,##0.00.

I want to be able to link the check box in B12 with the $ amount in D12 so that if I check the box saying this customer is tax exempt then D12 will read $0.00. Can I either:

(1) put a formula in D12 that says if check1 is true then put $0.00 in D12 and if not, then calculate the tax? If so, what would like formula look like? What I tried didn't work, 'Syntax Error'.
OR
(2) create a macro for D12 that does the same thing? If so, can you tell me the macro? I'm not very knowledgable about macros.

BTW, D12 was just created by inserting a formula via the Table Layout tab and selecting Formula. It wasn't created by inserting a text box using the Developer tab, so it doesn't have the ability to select Properties and then choose Run Macro upon Entry or Exit.

I would appreciate any help! Thank you.

Hi derby46a,

You can't reference a checkbox's state via field coding - a macro would be required. An alternative, that doesn't require macro coding, is to use a dropdown formfield with Yes/No options (or even checked/unchecked wingding characters). You can then test the dropdown formfield's state and vary the output accordingly.

To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial:
https://www.msofficeforums.com/word/...-tutorial.html

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hello macropod,

Thank you for your answer and tutorial. I'm interested in what you are suggesting, but I'm not sure what you mean by 'test the dropdown formfield's state and vary the output accordingly'.
I created a yes/no dropdown and then went to the Word Field Maths Tutorial you suggested and used a formula in there, =IF(DEFINE=YES, $0.00, D11*0.0835) but it didn't work. I didn't know what you were referring to in your Maths Tutorial that I should use so I just guessed it was the DEFINE function. I don't know if you were referring to the macro in there or not either. Can you be more specific re: what a formula would look like or what the macro would look like? I'm not too knowlegdable and need a little more help if you can.

Thank you, derby46a

See attached demo of a dropdown pretending to be a checkbox.

Attached Files

DropdownDemo.doc (39.5 KB, 94 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you for the example. I was trying to see the formula or macro behind how this works, though, and it's not letting me. Can you let me in on how the dropdown and the adjacent cell are linked, specifically, what formula or macro is used? I appreciate your answers.

Sincerely,
derby46a

Hi derby46a,

There are no macros in the file - the functionality is all done with field coding. To see how, remove the forms protection (via Developer|Restrict Editing), then press Alt-F9. Note that the field code for the dropdown 'checkbox' will appear as a jumble of Winding characters, as it's had that font applied.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you, macropod. Sorry for the delayed response. I unlocked the document and saw the formula behind the fields and I was trying to change it to my dropdown box which uses Yes and No vs. wingdings.

When you press ALT+F9 your document shows the code:
IF{REF Dropdown3 \* MERGEFORMAT }={QUOTE 254} {SUM(ABOVE)*0.1 \# "$,0.00"} 0 \# "$,0.00"

I tried to make my code look similar but w/ a Yes/No dropdown but it doesn't work. Can you help fix what is wrong? (My formula is different b/c it asks if client is tax exempt, so "No" means the tax needs to be calculated--and I took out the MERGEFORMAT switch):
IF{REF Dropdown1 }={QUOTE No} {PRODUCT (D12,.0835) \# "$#,##0.00;($#,##0.00)"} 0 \# "$,0.00"

I've tried {QUOTE "No"} or just "No", or No without quotations, any variety you can think of and I get Syntax Error. My dropdown defaults to No. On some of these variations I've tried the "Yes" option works and the adjacent cell will change to $0.00 like it should. I would appreciate you checking where I am wrong.

Thank you so much for your help so far!

derby46a

In the first example cited, the field's formula would become:
{IF{REF Dropdown3 \* MERGEFORMAT }= "Yes" {SUM(ABOVE)*0.1 \# "$,0.00"} 0 \# "$,0.00"}
Similarly, the second field's formula would become:
{IF{REF Dropdown1 }= "No" {=D12*.0835} 0 \# "$,0.00;($,0.00)"}

I note that, for the second example, you have switch expressions coded as '$#,##0.00'. This will add spaces between the $ symbol and the thousands separator for amounts < $100, but those spaces are unlikely to be much more than half the width as the digits they replace. If you need a separation between the $ symbol and the values, it is better to use a tab character, format the cell as left-aligned and use a right, or decimal, tab stop where needed.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hello macropod,

I'm sorry but the 2nd formula, which is the one I need, is not working when I try it (and I am using CTRL F9 to insert the brackets).:

{IF{REF Dropdown1 }= "No" {=D12*.0835} 0 \# "$,0.00;($,0.00)"}

I'm still using the '$#,##0.00' though, but I don't think that will affect the formula to not work.

When I try to test my document now, the cell with this formula doesn't register anything--no matter if I choose 'Yes' or 'No' from the dropdown in the cell just to the left of it. Can you think of why?

The other fields in the table have been added by going to Developer/Legacy Tools under 'Controls'/Text Form Field and dictated what Type, Text Format, Calculate on Exit, etc. in the Text Form Field Properties dialog box. Could this not be working b/c I've not added this form field like I have the other ones?

I would appreciate any further help. I'm so close to finishing this project. Could I fwd the table portion to you somehow?

Thank you very much,
derby46a

Hi derby46a,

The field codes are very particular about having spaces in certain places (eg either side of the "No"); if you omit them the code won't work. You also need to have the dropdown's 'calculate on exit' property checked.

You can attach a document to a post (delete anything sensitive) via the paperclip symbol on the 'Go Advanced' tab.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you, macropod. I went through my formula to check spacing, tried adding the formula through the Properties dialog box, and some other options but still no success.

I've attached just the table portion of my document to see if you can review where I went wrong. Thank you for looking this over!

P.S. I left the document unlocked.

Attached Files

TEST.docx (17.8 KB, 25 views)

Hi derby46a,

Your document is basically OK, though it didn't have forms protection applied. Once applied, the formulae seem to work OK. I've also simplified the document a bit (see attached).

The main change was to replace you calculation formfields with regular forumula fields. Calculation formfields produce strange results when other calculation formfields reference them.

I also deleted all your bookmarks, except for 'Dropdown1'. As your text formfields are all in a table and use numeric values for the calculations, the formula fields can reference their cell addresses directly. It is also unwise to use bookmark names that correspond with Word's cell referencing convention. Doing so can lead to invalid results in documents with more than one table, or even with no tables.

Attached Files

TEST.docx (17.3 KB, 63 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you so, so much, macropod. It works! Thank you for cleaning it up, too. I went through to look at what you cleaned up; I didn't know I had too much info in the table.
Also, I changed the number format back to your suggestion, \# "$,0.00;($,0.00)" in the tax cell, D13, and it works just fine.

What a load off my shoulders! Thank you, again!

P.S. I couldn't find where I check off that this is solved, or I didn't know if you do it on your end (?)

You'll find it under 'Thread Tools'. I've done it for this thread.

Cheers

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

macropod,

I have a similar issue. Once I signed on, I downloaded the Test (1).docx but it was locked. Can you send me the unlocked version, so I can see the code behind the solution?

Thansk!