Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
I have set up in MS Word a main document and source document, which performs
a mail merge to produce a Form Letter. The mail merge works OK and the appropriate Merge Fields are correctly inserted into the Form Letter. The main document is an advice to individuals and team leaders re competitions they wish to enter and includes a table, the first three columns of which are – Col A – Competition Name, Col B – Accepted and Col C Entry Fee. Row 1 contains the column headings and in Rows 2 to 12 the names of various competitions. In Row 13 cell C13 has the formula: - {=Sum(C2:C12)\#”£#,##0.00;(£#,##0.00)”} and correctly shows the total of any amounts (entered manually) in Col C. I am having problems in getting a conditional formula to work. In Col B an X will signify acceptance of an entry (in the competition for that row) and in Col C the formula is required to calculate the entry fee, which will vary according to the number of people in the team for that competition. I envisage something like: – {=IF B2=X Product (2.90,2)}. With an appropriate number format (#,##0.00) inserted in the formula dialog box this should produce 5.80 in cell C2 provided B2 has X, (or blank if not X), and likewise in other cells in Col C with a similar formula. I know the ‘curly brackets’ have to be inserted by using Ctrl + F9 and not typed in from the keyboard. However all my attempts seem to produce a Syntax Error in cell C2. On one occasion I did manage to update C2 by using F9 but when I removed the X from B2 and did another update the 5.90 remained in C2 (instead of a blank because there was no acceptance for that competition). The following formula: - {IF {B2X,””,{=PRODUCT(2.9,2)\#”#,##0.00”}}} was accepted in C2 without any error message, but when the cell was selected and updated by F9 no result was displayed whether or not B2 had an X. I then tried inserting an = sign before the IF and was rewarded with: - !Unexpected end of formula. All suggestions for resolving this problem gratefully received. |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Please can someone tell me what is wrong with the syntax in the following
formula: - {=IF(B3="X",PRODUCT(2.9,2),"")} The ’curly brackets’ (braces) were inserted into the cell using Ctrl + F9. I constructed the formula in the cell originally from the keyboard and got a ! Syntax Error, “ message. I then deleted the formula and constructed the formula in an Excel worksheet and it worked. I then copied the formula to the clipboard, went back to the (Word 2000) document table, inserted the braces into the cell and then pasted the formula between the braces and again got the same error message as before. Does anyone know the significance of the double quotes at the end of the message? From experimenting it seems the problem is to do with the IF condition, because a straightforward function - PRODUCT(2.9,2) - with appropriate number formatting gives the correct result i.e. 5.80 To get the job finished I have had to manually update my Form Letter but would like to get this problem sorted so that I can automate the job next time I use it. Peter -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
I think you're trying to use Excel syntax in Word. Word's syntax would be:
{ IF B3 = "X" { = PRODUCT (2.9,2) } "" } or just { IF B3 = "X" { = 2.9 * 2 } "" } -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA Word MVP FAQ site: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5e5ece24139be@uwe... Please can someone tell me what is wrong with the syntax in the following formula: - {=IF(B3="X",PRODUCT(2.9,2),"")} The ’curly brackets’ (braces) were inserted into the cell using Ctrl + F9. I constructed the formula in the cell originally from the keyboard and got a ! Syntax Error, “ message. I then deleted the formula and constructed the formula in an Excel worksheet and it worked. I then copied the formula to the clipboard, went back to the (Word 2000) document table, inserted the braces into the cell and then pasted the formula between the braces and again got the same error message as before. Does anyone know the significance of the double quotes at the end of the message? From experimenting it seems the problem is to do with the IF condition, because a straightforward function - PRODUCT(2.9,2) - with appropriate number formatting gives the correct result i.e. 5.80 To get the job finished I have had to manually update my Form Letter but would like to get this problem sorted so that I can automate the job next time I use it. Peter -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Suzanne
Thanks for your response. If one tries to construct the formula from the Word menu bar - Table Formula, the Formula dialogue appears and any formula constructed appears in the cell with a pair of braces around it. However if one pastes the IF function into the formula line it then appears as "=IF( )" (without the quotes of course). I have been unable to find any way to insert a pair of braces in the formula line. So I have constructed the formula directly in the table cell using Ctrl + F9 to insert the braces then keying in your suggested syntax. The formula was accepted into the cell without any error message, but when I selected the cell and updated it with F9 the cell remained blank even though there was an X in B3. Your alternative also performed similarly. It seems the inclusion of spaces does make a difference. In the following I have put asterisks where spaces occur: - {*IF*B2*=*"X"*{*=*PRODUCT(2.9,2),*}""*} This gives a "no result" i.e. the cell is blank when updated. {*IF*B2="X"*{*=*PRODUCT(2.9,2),*}""*} Removing the spaces either side of the first "=" causes a X to appear in the cell when it is updated. Unfortunately I am no further forward in finding what is wrong with the syntax of my version. Please may I ask you if you have actually tried to get this working on your system; I would be very interested to know. Suzanne S. Barnhill wrote: I think you're trying to use Excel syntax in Word. Word's syntax would be: { IF B3 = "X" { = PRODUCT (2.9,2) } "" } or just { IF B3 = "X" { = 2.9 * 2 } "" } -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Well, this was pretty bizarre, but here's the answer:
{ IF B2=X { = PRODUCT (2.9,2} "" } That is, remove the quotes around "X." The spaces around the first = are crucial, but none of the other spaces matter, and it works with or without quotes around the IF field. In most case spaces make no difference; Word inserts and in some cases insists on spaces between the field delimiters and the contents; spaces around operators can be more touchy. Also, I realize this is probably just a sample/example/test, but if your actual field were going to be the product of 2.9 and 2, it would make a lot more sense to just put "5.8" instead of { = PRODUCT (2.9,2) }. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA Word MVP FAQ site: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5e648d56f3b52@uwe... Hi Suzanne Thanks for your response. If one tries to construct the formula from the Word menu bar - Table Formula, the Formula dialogue appears and any formula constructed appears in the cell with a pair of braces around it. However if one pastes the IF function into the formula line it then appears as "=IF( )" (without the quotes of course). I have been unable to find any way to insert a pair of braces in the formula line. So I have constructed the formula directly in the table cell using Ctrl + F9 to insert the braces then keying in your suggested syntax. The formula was accepted into the cell without any error message, but when I selected the cell and updated it with F9 the cell remained blank even though there was an X in B3. Your alternative also performed similarly. It seems the inclusion of spaces does make a difference. In the following I have put asterisks where spaces occur: - {*IF*B2*=*"X"*{*=*PRODUCT(2.9,2),*}""*} This gives a "no result" i.e. the cell is blank when updated. {*IF*B2="X"*{*=*PRODUCT(2.9,2),*}""*} Removing the spaces either side of the first "=" causes a X to appear in the cell when it is updated. Unfortunately I am no further forward in finding what is wrong with the syntax of my version. Please may I ask you if you have actually tried to get this working on your system; I would be very interested to know. Suzanne S. Barnhill wrote: I think you're trying to use Excel syntax in Word. Word's syntax would be: { IF B3 = "X" { = PRODUCT (2.9,2) } "" } or just { IF B3 = "X" { = 2.9 * 2 } "" } -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Peterkins,
A formula in Column C like: {IF B2=X {=2*2.9} \# £,0.00} should give the result you're after. However, this requires hard-coding both the entry fee and the number of entrants. If you set your table up so that Column C has the number of entries, and Column D has the fee per entry, you could use: {IF B2=X {=C2*D2} \# £,0.00} in Column E. For more on Word field maths, check out my Word Field Maths 'tutorial', at: http://www.wopr.com/cgi-bin/w3t/show...?Number=365442 Cheers "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5e648d56f3b52@uwe... Hi Suzanne Thanks for your response. If one tries to construct the formula from the Word menu bar - Table Formula, the Formula dialogue appears and any formula constructed appears in the cell with a pair of braces around it. However if one pastes the IF function into the formula line it then appears as "=IF( )" (without the quotes of course). I have been unable to find any way to insert a pair of braces in the formula line. So I have constructed the formula directly in the table cell using Ctrl + F9 to insert the braces then keying in your suggested syntax. The formula was accepted into the cell without any error message, but when I selected the cell and updated it with F9 the cell remained blank even though there was an X in B3. Your alternative also performed similarly. It seems the inclusion of spaces does make a difference. In the following I have put asterisks where spaces occur: - {*IF*B2*=*"X"*{*=*PRODUCT(2.9,2),*}""*} This gives a "no result" i.e. the cell is blank when updated. {*IF*B2="X"*{*=*PRODUCT(2.9,2),*}""*} Removing the spaces either side of the first "=" causes a X to appear in the cell when it is updated. Unfortunately I am no further forward in finding what is wrong with the syntax of my version. Please may I ask you if you have actually tried to get this working on your system; I would be very interested to know. Suzanne S. Barnhill wrote: I think you're trying to use Excel syntax in Word. Word's syntax would be: { IF B3 = "X" { = PRODUCT (2.9,2) } "" } or just { IF B3 = "X" { = 2.9 * 2 } "" } -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Suzanne
This problem seems to be getting even more bizarre. First if I may explain I used the Product function because the Entry Fee can vary from time to time. Because I was trying to get this thing working by taking one step at a time, I used a fixed fee in the formula, but intended to substitute the result of an ASK field into the formula once the formula is made to work properly. My main document has two ASK fields one for a date and the other for the Entry Fee. As you doubtless know, if the whole document is selected (Ctrl + A) and F9 to update, Word displays the prompts for the ASK fields which are then updated along with the results from the formulae in each of the rows in Column C. I used your revised syntax first with the fixed fee and then substituting the ASK field { EntryFee } in the formula and got the correct results with both versions and using various amounts for fees. However once an update had produced a value in C, removing the X from Col B and again updating, the previously calculated value remained in C. I made some further experiments and then found that the calculation is performed whether or not there is an X in Col B!! This is how my formula now looks (* = space): - {*IF*B2=X*{*PRODUCT( {*EntryFee*},2)\#*#0.00*}""*} What further thoughts do you have please? With many thanks for your time and patience. Peter Suzanne S. Barnhill wrote: Well, this was pretty bizarre, but here's the answer: { IF B2=X { = PRODUCT (2.9,2} "" } That is, remove the quotes around "X." The spaces around the first = are crucial, but none of the other spaces matter, and it works with or without quotes around the IF field. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#8
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Peterkins,
Table cell referencing doesn't work with text strings. To refer to a character string in a cell, the character string (not the cell itself) must be individually bookmarked and the bookmark referred to in the reference. So my previous post on this was wrong (Should have read my own tutorial)! So, if you bookmark the text range in cell B2 with something like 'Cell2B' you could use a field coded like: {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} Since maintaining the bookmark in such a field can be a PITA, you might find it easiest to add a FILLIN field to cell B2 and bookmark that. Code the FILLIN field something like: {FILLIN "Enter 'X' if accepted"} Cheers "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5e6c0184d18e9@uwe... Hi Suzanne This problem seems to be getting even more bizarre. First if I may explain I used the Product function because the Entry Fee can vary from time to time. Because I was trying to get this thing working by taking one step at a time, I used a fixed fee in the formula, but intended to substitute the result of an ASK field into the formula once the formula is made to work properly. My main document has two ASK fields one for a date and the other for the Entry Fee. As you doubtless know, if the whole document is selected (Ctrl + A) and F9 to update, Word displays the prompts for the ASK fields which are then updated along with the results from the formulae in each of the rows in Column C. I used your revised syntax first with the fixed fee and then substituting the ASK field { EntryFee } in the formula and got the correct results with both versions and using various amounts for fees. However once an update had produced a value in C, removing the X from Col B and again updating, the previously calculated value remained in C. I made some further experiments and then found that the calculation is performed whether or not there is an X in Col B!! This is how my formula now looks (* = space): - {*IF*B2=X*{*PRODUCT( {*EntryFee*},2)\#*#0.00*}""*} What further thoughts do you have please? With many thanks for your time and patience. Peter Suzanne S. Barnhill wrote: Well, this was pretty bizarre, but here's the answer: { IF B2=X { = PRODUCT (2.9,2} "" } That is, remove the quotes around "X." The spaces around the first = are crucial, but none of the other spaces matter, and it works with or without quotes around the IF field. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#9
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi macropod
Thanks for your help, but I am still a bit stuck. I have not had experience with bookmarking before but have tried to follow your instructions. Although I don't quite understand how/why maintaining a book mark can be a pain, I opted for your suggestion to create a FILLIN field and this is what I have entered (again * = space): - {*FILLIN*"Enter*X*if*entering*Men's*Singles"\d**\* *MERGEFORMAT*} (I don't understand the Mergeformat bit but presume it has been put there by Word because this is a main document for a mail merge.) I selected all of the Fillin and using Insert Bookmark named it CellB2. The formula in C2 is now: - {*IF*{*CellB2*}=X*{*PRODUCT({*EntryFee*},2)\#*#0. 00*}""*} I ran a mail merge and after answering the ASK prompts I had an error message "A field calculation error occurred in record " for each record in the source document, and all the sections of the resulting Form Letter had "=X" (without quotes) in C2. I didn't get any prompt to "Enter X if entering Men's Singles" even thought the FILLIN switch was set to \d. Any further ideas please? Regards Peter macropod wrote: Hi Peterkins, Table cell referencing doesn't work with text strings. To refer to a character string in a cell, the character string (not the cell itself) must be individually bookmarked and the bookmark referred to in the reference. So my previous post on this was wrong (Should have read my own tutorial)! So, if you bookmark the text range in cell B2 with something like 'Cell2B' you could use a field coded like: {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} Since maintaining the bookmark in such a field can be a PITA, you might find it easiest to add a FILLIN field to cell B2 and bookmark that. Code the FILLIN field something like: {FILLIN "Enter 'X' if accepted"} -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#10
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
You might try an ASK field rather than a fill-in. That will let you set the
bookmark directly. This makes up for the bookmark's fragility. You can find an ASK and FILL-IN field tutorial at http://addbalance.com/word/download.htm. I would suggest adding spaces around the equals sign and quotation marks around the comparison items and around your True result as well. I also added a REF field. {*IF*"{*REF*CellB2*}"*=*"X"*"{*PRODUCT({*EntryFee* },2)\#*#0.00*}"*""*} -- Charles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (supplemented version of Microsoft's Legal Users' Guide) http://addbalance.com/usersguide See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome! --------- --------- --------- --------- --------- --------- This message is posted to a newsgroup. Please post replies and questions to the newsgroup so that others can learn from my ignorance and your wisdom. "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5e6d9e84e6a5d@uwe... Hi macropod Thanks for your help, but I am still a bit stuck. I have not had experience with bookmarking before but have tried to follow your instructions. Although I don't quite understand how/why maintaining a book mark can be a pain, I opted for your suggestion to create a FILLIN field and this is what I have entered (again * = space): - {*FILLIN*"Enter*X*if*entering*Men's*Singles"\d**\* *MERGEFORMAT*} (I don't understand the Mergeformat bit but presume it has been put there by Word because this is a main document for a mail merge.) I selected all of the Fillin and using Insert Bookmark named it CellB2. The formula in C2 is now: - {*IF*{*CellB2*}=X*{*PRODUCT({*EntryFee*},2)\#*#0. 00*}""*} I ran a mail merge and after answering the ASK prompts I had an error message "A field calculation error occurred in record " for each record in the source document, and all the sections of the resulting Form Letter had "=X" (without quotes) in C2. I didn't get any prompt to "Enter X if entering Men's Singles" even thought the FILLIN switch was set to \d. Any further ideas please? Regards Peter macropod wrote: Hi Peterkins, Table cell referencing doesn't work with text strings. To refer to a character string in a cell, the character string (not the cell itself) must be individually bookmarked and the bookmark referred to in the reference. So my previous post on this was wrong (Should have read my own tutorial)! So, if you bookmark the text range in cell B2 with something like 'Cell2B' you could use a field coded like: {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} Since maintaining the bookmark in such a field can be a PITA, you might find it easiest to add a FILLIN field to cell B2 and bookmark that. Code the FILLIN field something like: {FILLIN "Enter 'X' if accepted"} -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#11
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Charles,
I had thought of an ASK field, but it only sets a bookmark - the results are hidden unless you use yet another field (REF) to display them. Hence the bookmarked FILLIN field. Cheers "Charles Kenyon" wrote in message ... You might try an ASK field rather than a fill-in. That will let you set the bookmark directly. This makes up for the bookmark's fragility. You can find an ASK and FILL-IN field tutorial at http://addbalance.com/word/download.htm. I would suggest adding spaces around the equals sign and quotation marks around the comparison items and around your True result as well. I also added a REF field. {*IF*"{*REF*CellB2*}"*=*"X"*"{*PRODUCT({*EntryFee* },2)\#*#0.00*}"*""*} -- Charles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (supplemented version of Microsoft's Legal Users' Guide) http://addbalance.com/usersguide See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome! --------- --------- --------- --------- --------- --------- This message is posted to a newsgroup. Please post replies and questions to the newsgroup so that others can learn from my ignorance and your wisdom. "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5e6d9e84e6a5d@uwe... Hi macropod Thanks for your help, but I am still a bit stuck. I have not had experience with bookmarking before but have tried to follow your instructions. Although I don't quite understand how/why maintaining a book mark can be a pain, I opted for your suggestion to create a FILLIN field and this is what I have entered (again * = space): - {*FILLIN*"Enter*X*if*entering*Men's*Singles"\d**\* *MERGEFORMAT*} (I don't understand the Mergeformat bit but presume it has been put there by Word because this is a main document for a mail merge.) I selected all of the Fillin and using Insert Bookmark named it CellB2. The formula in C2 is now: - {*IF*{*CellB2*}=X*{*PRODUCT({*EntryFee*},2)\#*#0. 00*}""*} I ran a mail merge and after answering the ASK prompts I had an error message "A field calculation error occurred in record " for each record in the source document, and all the sections of the resulting Form Letter had "=X" (without quotes) in C2. I didn't get any prompt to "Enter X if entering Men's Singles" even thought the FILLIN switch was set to \d. Any further ideas please? Regards Peter macropod wrote: Hi Peterkins, Table cell referencing doesn't work with text strings. To refer to a character string in a cell, the character string (not the cell itself) must be individually bookmarked and the bookmark referred to in the reference. So my previous post on this was wrong (Should have read my own tutorial)! So, if you bookmark the text range in cell B2 with something like 'Cell2B' you could use a field coded like: {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} Since maintaining the bookmark in such a field can be a PITA, you might find it easiest to add a FILLIN field to cell B2 and bookmark that. Code the FILLIN field something like: {FILLIN "Enter 'X' if accepted"} -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#12
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi macropod
I have just read your reply to Charles, whose suggestions I was still trying to get to grips with - I am in unfamiliar territory here. Please can you explain where I have gone wrong in getting the results from using your ideas, which I posted above @ 16:24hrs. Regards Peter macropod wrote: Hi Charles, I had thought of an ASK field, but it only sets a bookmark - the results are hidden unless you use yet another field (REF) to display them. Hence the bookmarked FILLIN field. Cheers You might try an ASK field rather than a fill-in. That will let you set the bookmark directly. This makes up for the bookmark's fragility. You can find [quoted text clipped - 58 lines] FILLIN field something like: {FILLIN "Enter 'X' if accepted"} -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#13
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Peterkins,
You can safely delete the \* MERGEFORMAT switch. For the formula, use any of: {IF{Cell2B}= X {=EntryFee*2} \# ,0.00} {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# ,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# £,0.00} Note that you mast have a '=' sign in the second field, and the false output (ie "") isn't necessary. Cheers NB: You must have the spaces shown above, though additional spaces in *some* places won't hurt, and you can put the numeric picture switch inside the second field. "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5e6d9e84e6a5d@uwe... Hi macropod Thanks for your help, but I am still a bit stuck. I have not had experience with bookmarking before but have tried to follow your instructions. Although I don't quite understand how/why maintaining a book mark can be a pain, I opted for your suggestion to create a FILLIN field and this is what I have entered (again * = space): - {*FILLIN*"Enter*X*if*entering*Men's*Singles"\d**\* *MERGEFORMAT*} (I don't understand the Mergeformat bit but presume it has been put there by Word because this is a main document for a mail merge.) I selected all of the Fillin and using Insert Bookmark named it CellB2. The formula in C2 is now: - {*IF*{*CellB2*}=X*{*PRODUCT({*EntryFee*},2)\#*#0. 00*}""*} I ran a mail merge and after answering the ASK prompts I had an error message "A field calculation error occurred in record " for each record in the source document, and all the sections of the resulting Form Letter had "=X" (without quotes) in C2. I didn't get any prompt to "Enter X if entering Men's Singles" even thought the FILLIN switch was set to \d. Any further ideas please? Regards Peter macropod wrote: Hi Peterkins, Table cell referencing doesn't work with text strings. To refer to a character string in a cell, the character string (not the cell itself) must be individually bookmarked and the bookmark referred to in the reference. So my previous post on this was wrong (Should have read my own tutorial)! So, if you bookmark the text range in cell B2 with something like 'Cell2B' you could use a field coded like: {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} Since maintaining the bookmark in such a field can be a PITA, you might find it easiest to add a FILLIN field to cell B2 and bookmark that. Code the FILLIN field something like: {FILLIN "Enter 'X' if accepted"} -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#14
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi macropod
Sorry for the delay in coming back to this forum, but have been rather busy for the last few days. I am sorry to say I am still having great difficulty in resolving this problem. The formula in C2 is now: - {*IF{*CellB2*}=X*{=PRODUCT(EntryFee,1)*}*\#*#0.00} and in C3 a similar formula but with a 2 instead of a 1 after the EntryFee. The contents of B2 are [{*FILLIN*"Enter*X*for*Men's*Singles"\d*}] with a bookmark title of CellB2. A similar Filllin in C2 with Pairs in place of Singles has been bookmarked as CellB3. Running a mail merge first prompts for each of the ASK fields, and then immediately issues 'A field calculation error occurred for record ' for each of the selected records in the source document. The resultant Form Letter has the correct calculation in C2 and C3 (i.e. EntryFee x 1 or x2), but these appear in those cells in EACH of the sections. Since no prompts were issued for the Fillins this is probably not surprising, except that I would have thought that since there is no X in B2 or B3 the formula result would have been 'False' and therefore C2 and C3 should have remained blank. It seems that the Fillin function is not working correctly. At first I thought this might be the cause of the error message, but since I only get one error message per record, and not one for each Fillin for each record I now have my doubts. I would be grateful if you, or anyone else, can identify why this feature is not working. Regards Peter macropod wrote: Hi Peterkins, You can safely delete the \* MERGEFORMAT switch. For the formula, use any of: {IF{Cell2B}= X {=EntryFee*2} \# ,0.00} {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# ,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# £,0.00} Note that you mast have a '=' sign in the second field, and the false output (ie "") isn't necessary. -- Message posted via http://www.officekb.com |
#15
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Peterkins,
If you're using a FILLIN field in a mailmerge, you *should* get a prompt for each record, unless you've added the \o switch to the FILLIN field. I didn't see any mention of a mailmerge before your latest post. This raises additional issues, since each merged record will be setting the same bookmark, and (subject to the above), the merged output will end up holding only the last bookmark value. If the FILLIN fields are still there in your merged output, you could use Ctrl-A then F9 to update them all after merging, following which I'd either lock the fields via Ctrl-F11, or permanently convert them to their calculated values via Ctrl-Shift-F9. Cheers "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5eabff5860a06@uwe... Hi macropod Sorry for the delay in coming back to this forum, but have been rather busy for the last few days. I am sorry to say I am still having great difficulty in resolving this problem. The formula in C2 is now: - {*IF{*CellB2*}=X*{=PRODUCT(EntryFee,1)*}*\#*#0.00} and in C3 a similar formula but with a 2 instead of a 1 after the EntryFee. The contents of B2 are [{*FILLIN*"Enter*X*for*Men's*Singles"\d*}] with a bookmark title of CellB2. A similar Filllin in C2 with Pairs in place of Singles has been bookmarked as CellB3. Running a mail merge first prompts for each of the ASK fields, and then immediately issues 'A field calculation error occurred for record ' for each of the selected records in the source document. The resultant Form Letter has the correct calculation in C2 and C3 (i.e. EntryFee x 1 or x2), but these appear in those cells in EACH of the sections. Since no prompts were issued for the Fillins this is probably not surprising, except that I would have thought that since there is no X in B2 or B3 the formula result would have been 'False' and therefore C2 and C3 should have remained blank. It seems that the Fillin function is not working correctly. At first I thought this might be the cause of the error message, but since I only get one error message per record, and not one for each Fillin for each record I now have my doubts. I would be grateful if you, or anyone else, can identify why this feature is not working. Regards Peter macropod wrote: Hi Peterkins, You can safely delete the \* MERGEFORMAT switch. For the formula, use any of: {IF{Cell2B}= X {=EntryFee*2} \# ,0.00} {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# ,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# £,0.00} Note that you mast have a '=' sign in the second field, and the false output (ie "") isn't necessary. -- Message posted via http://www.officekb.com |
#16
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Nope. I haven't followed this entire thread so I hope you'll forgive me if
I'm off base. When an Ask of a FillIn field is used in a mail merge, where the box isn't checked to Ask Once, it will be asked each time. Further, where a Ref field is to a bookmark which contains a FillIn field, the Ref field will give the result for each answer in turn. As each merge is performed, the Ref field is unlinked so the reference will reflect each answer rather than only the last one. -- Charles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (supplemented version of Microsoft's Legal Users' Guide) http://addbalance.com/usersguide See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome! My criminal defense site: http://addbalance.com --------- --------- --------- --------- --------- --------- This message is posted to a newsgroup. Please post replies and questions to the newsgroup so that others can learn from my ignorance and your wisdom. "macropod" wrote in message ... Hi Peterkins, If you're using a FILLIN field in a mailmerge, you *should* get a prompt for each record, unless you've added the \o switch to the FILLIN field. I didn't see any mention of a mailmerge before your latest post. This raises additional issues, since each merged record will be setting the same bookmark, and (subject to the above), the merged output will end up holding only the last bookmark value. If the FILLIN fields are still there in your merged output, you could use Ctrl-A then F9 to update them all after merging, following which I'd either lock the fields via Ctrl-F11, or permanently convert them to their calculated values via Ctrl-Shift-F9. Cheers "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5eabff5860a06@uwe... Hi macropod Sorry for the delay in coming back to this forum, but have been rather busy for the last few days. I am sorry to say I am still having great difficulty in resolving this problem. The formula in C2 is now: - {*IF{*CellB2*}=X*{=PRODUCT(EntryFee,1)*}*\#*#0.00} and in C3 a similar formula but with a 2 instead of a 1 after the EntryFee. The contents of B2 are [{*FILLIN*"Enter*X*for*Men's*Singles"\d*}] with a bookmark title of CellB2. A similar Filllin in C2 with Pairs in place of Singles has been bookmarked as CellB3. Running a mail merge first prompts for each of the ASK fields, and then immediately issues 'A field calculation error occurred for record ' for each of the selected records in the source document. The resultant Form Letter has the correct calculation in C2 and C3 (i.e. EntryFee x 1 or x2), but these appear in those cells in EACH of the sections. Since no prompts were issued for the Fillins this is probably not surprising, except that I would have thought that since there is no X in B2 or B3 the formula result would have been 'False' and therefore C2 and C3 should have remained blank. It seems that the Fillin function is not working correctly. At first I thought this might be the cause of the error message, but since I only get one error message per record, and not one for each Fillin for each record I now have my doubts. I would be grateful if you, or anyone else, can identify why this feature is not working. Regards Peter macropod wrote: Hi Peterkins, You can safely delete the \* MERGEFORMAT switch. For the formula, use any of: {IF{Cell2B}= X {=EntryFee*2} \# ,0.00} {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# ,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# £,0.00} Note that you mast have a '=' sign in the second field, and the false output (ie "") isn't necessary. -- Message posted via http://www.officekb.com |
#17
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Thanks Charles,
The situation Peterkins described indicates the ASK field fires repeatedly but the FILLIN field was firing only once. To me, this implies the likely presence of the \o switch on the FILLIN field. As for the impact of the merged output, not having used a FILLIN (or ASK) field in a mailmerge, I wasn't sure how much would survive the merge process. Cheers "Charles Kenyon" wrote in message ... Nope. I haven't followed this entire thread so I hope you'll forgive me if I'm off base. When an Ask of a FillIn field is used in a mail merge, where the box isn't checked to Ask Once, it will be asked each time. Further, where a Ref field is to a bookmark which contains a FillIn field, the Ref field will give the result for each answer in turn. As each merge is performed, the Ref field is unlinked so the reference will reflect each answer rather than only the last one. -- Charles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (supplemented version of Microsoft's Legal Users' Guide) http://addbalance.com/usersguide See also the MVP FAQ: http://word.mvps.org/FAQs/ which is awesome! My criminal defense site: http://addbalance.com --------- --------- --------- --------- --------- --------- This message is posted to a newsgroup. Please post replies and questions to the newsgroup so that others can learn from my ignorance and your wisdom. "macropod" wrote in message ... Hi Peterkins, If you're using a FILLIN field in a mailmerge, you *should* get a prompt for each record, unless you've added the \o switch to the FILLIN field. I didn't see any mention of a mailmerge before your latest post. This raises additional issues, since each merged record will be setting the same bookmark, and (subject to the above), the merged output will end up holding only the last bookmark value. If the FILLIN fields are still there in your merged output, you could use Ctrl-A then F9 to update them all after merging, following which I'd either lock the fields via Ctrl-F11, or permanently convert them to their calculated values via Ctrl-Shift-F9. Cheers "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5eabff5860a06@uwe... Hi macropod Sorry for the delay in coming back to this forum, but have been rather busy for the last few days. I am sorry to say I am still having great difficulty in resolving this problem. The formula in C2 is now: - {*IF{*CellB2*}=X*{=PRODUCT(EntryFee,1)*}*\#*#0.00} and in C3 a similar formula but with a 2 instead of a 1 after the EntryFee. The contents of B2 are [{*FILLIN*"Enter*X*for*Men's*Singles"\d*}] with a bookmark title of CellB2. A similar Filllin in C2 with Pairs in place of Singles has been bookmarked as CellB3. Running a mail merge first prompts for each of the ASK fields, and then immediately issues 'A field calculation error occurred for record ' for each of the selected records in the source document. The resultant Form Letter has the correct calculation in C2 and C3 (i.e. EntryFee x 1 or x2), but these appear in those cells in EACH of the sections. Since no prompts were issued for the Fillins this is probably not surprising, except that I would have thought that since there is no X in B2 or B3 the formula result would have been 'False' and therefore C2 and C3 should have remained blank. It seems that the Fillin function is not working correctly. At first I thought this might be the cause of the error message, but since I only get one error message per record, and not one for each Fillin for each record I now have my doubts. I would be grateful if you, or anyone else, can identify why this feature is not working. Regards Peter macropod wrote: Hi Peterkins, You can safely delete the \* MERGEFORMAT switch. For the formula, use any of: {IF{Cell2B}= X {=EntryFee*2} \# ,0.00} {IF{Cell2B}= X {=EntryFee*2} \# £,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# ,0.00} {IF{Cell2B}= X {=PRODUCT(EntryFee,2)} \# £,0.00} Note that you mast have a '=' sign in the second field, and the false output (ie "") isn't necessary. -- Message posted via http://www.officekb.com |
#18
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi macropod and Charles
First of all I would like to correct a typo in my post above dated 12 April. In the third paragraph, the second FILLIN named CellB3 I stated to be in cell C2, but that should of course read cell B3. Also I should state that I am using the version of Word that comes with MS Office Professional 2000. Regarding your last response, with respect, I would point out that I *did* refer to mail merge in my post which started this thread. Also regarding the switch for the FILLIN’s I showed this in my posts on 07 April and 12 April as \d – i.e. to prompt for *every* record being merged from the source document. It might help if I explain in more detail exactly what I am trying to do. I belong to a bowls club (lawn green *not* ten-pin) some of whose members enter National Competitions. There are four basic competitions (Singles – 1 entrant, Pairs – 2 in a team, Triples – 3 in a team and Fours – 4 in a team) and also age related variants on these. I have a database with columns for the variables – name, address, date of birth etc – and another column in which I put an X to select that particular record for the mail merge. There is a record for each of the single entrants and team leaders with their relevant details. The main document has places amongst the text for the insertion of the relevant mail merge fields and also has a table with a row for each of the competitions. I have an ASK field that prompts *once only* for the for the competition year, and another ASK prompting *once only* for the Entry Fee per person which can vary each year. From a list of names who are entering the competitions I select (with an X) the relevant records in the database. Running the mail merge produces a Form Letter with one section for each of the selected source records. Initially I proposed to key in an X in Column B of each section for the competitions entered by that person or his teams. Then the formula in Column C on finding an X in Column B would calculate the total Entry Fee and when the whole document was updated using F9 the results of the calculation would be seen in Column C. When the formula would not recognise the character X, I posted the problem in this forum and then adopted your suggestion of using FILLIN’s. Once I can get that working properly I will put FILLIN’s in *each* of the Column B cells with appropriate wording for the competition for that row. To know which source record I am at, I would also include in the FILLIN prompt wording the { MERGEFIELD LastName } field. I would then expect to get only one prompt for each of the ASK fields (as I do at present) and ONE prompt for EACH FILLIN for EACH source record as it is merged. If the person is not entering the competition for that FILLIN prompt I would press the space bar (or Enter key?) so a blank would go in Column B. I hope the above will enable you to suggest why I am getting NO PROMPTS AT ALL from the FILLIN’s only an error message about a field calculation error for EACH selected source record. In fact when the formula does the calculations the results are correct, but they are done for EACH source record irrespective of an X or blank in the cells in Column B. If it would help I am willing to forward to you the files for the source and main documents for you to try running the mail merge. If you agree can you give me an address they can be sent to please? Regards Peter Charles Kenyon wrote: Nope. I haven't followed this entire thread so I hope you'll forgive me if I'm off base. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#19
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Peterkins,
The FILLIN field's '\d' switch doesn't cause it to prompt for *every* record being merged - it simply sets a default value which, in the field codes you've posted, is undefined. That's most likely the cause of your problem, since it typically prevents the FILLIN field from prompting for an update. To correct this, you could delete the '\d', though you might prefer to change it to '\d "X"', so that the "X" response becomes the default. Cheers PS: I wasn't saying you hadn't mentioned doing a mailmerge, just that I hadn't *seen* the reference beforehand. "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5ed211c1b7c8a@uwe... Hi macropod and Charles First of all I would like to correct a typo in my post above dated 12 April. In the third paragraph, the second FILLIN named CellB3 I stated to be in cell C2, but that should of course read cell B3. Also I should state that I am using the version of Word that comes with MS Office Professional 2000. Regarding your last response, with respect, I would point out that I *did* refer to mail merge in my post which started this thread. Also regarding the switch for the FILLIN’s I showed this in my posts on 07 April and 12 April as \d – i.e. to prompt for *every* record being merged from the source document. It might help if I explain in more detail exactly what I am trying to do. I belong to a bowls club (lawn green *not* ten-pin) some of whose members enter National Competitions. There are four basic competitions (Singles – 1 entrant, Pairs – 2 in a team, Triples – 3 in a team and Fours – 4 in a team) and also age related variants on these. I have a database with columns for the variables – name, address, date of birth etc – and another column in which I put an X to select that particular record for the mail merge. There is a record for each of the single entrants and team leaders with their relevant details. The main document has places amongst the text for the insertion of the relevant mail merge fields and also has a table with a row for each of the competitions. I have an ASK field that prompts *once only* for the for the competition year, and another ASK prompting *once only* for the Entry Fee per person which can vary each year. From a list of names who are entering the competitions I select (with an X) the relevant records in the database. Running the mail merge produces a Form Letter with one section for each of the selected source records. Initially I proposed to key in an X in Column B of each section for the competitions entered by that person or his teams. Then the formula in Column C on finding an X in Column B would calculate the total Entry Fee and when the whole document was updated using F9 the results of the calculation would be seen in Column C. When the formula would not recognise the character X, I posted the problem in this forum and then adopted your suggestion of using FILLIN’s. Once I can get that working properly I will put FILLIN’s in *each* of the Column B cells with appropriate wording for the competition for that row. To know which source record I am at, I would also include in the FILLIN prompt wording the { MERGEFIELD LastName } field. I would then expect to get only one prompt for each of the ASK fields (as I do at present) and ONE prompt for EACH FILLIN for EACH source record as it is merged. If the person is not entering the competition for that FILLIN prompt I would press the space bar (or Enter key?) so a blank would go in Column B. I hope the above will enable you to suggest why I am getting NO PROMPTS AT ALL from the FILLIN’s only an error message about a field calculation error for EACH selected source record. In fact when the formula does the calculations the results are correct, but they are done for EACH source record irrespective of an X or blank in the cells in Column B. If it would help I am willing to forward to you the files for the source and main documents for you to try running the mail merge. If you agree can you give me an address they can be sent to please? Regards Peter Charles Kenyon wrote: Nope. I haven't followed this entire thread so I hope you'll forgive me if I'm off base. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#20
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi macropod
Phew!! Managed to get it working at last! Having inserted the default "X" after the \ d switch in each of the FILLIN fields the mail merge job works like a dream - well spotted macropod! For any other readers of this thread who may have a similar problem, when closing the main document after the mail merge DO NOT SAVE THE CHANGES, otherwise you will probably find some of the results embedded in that document. I am very grateful to you macropod for your time and patience in getting me sorted out on this problem which was becoming a PITA, but perseverance won in the end. Regards Peter macropod wrote: Hi Peterkins, The FILLIN field's '\d' switch doesn't cause it to prompt for *every* record being merged - it simply sets a default value which, in the field codes you've posted, is undefined. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
#21
Posted to microsoft.public.word.tables
|
|||
|
|||
MS Word table - problem with conditional formula
Hi Peterkins,
Glad to see you've got it sorted. BTW, you could also set the '\d' switch to '\d ""', so that the "" (empty/nul) response becomes the default. Cheers "Peterkins via OfficeKB.com" u20492@uwe wrote in message news:5eec677118b81@uwe... Hi macropod Phew!! Managed to get it working at last! Having inserted the default "X" after the \ d switch in each of the FILLIN fields the mail merge job works like a dream - well spotted macropod! For any other readers of this thread who may have a similar problem, when closing the main document after the mail merge DO NOT SAVE THE CHANGES, otherwise you will probably find some of the results embedded in that document. I am very grateful to you macropod for your time and patience in getting me sorted out on this problem which was becoming a PITA, but perseverance won in the end. Regards Peter macropod wrote: Hi Peterkins, The FILLIN field's '\d' switch doesn't cause it to prompt for *every* record being merged - it simply sets a default value which, in the field codes you've posted, is undefined. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...ables/200604/1 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WP merge file to Word | Tables | |||
How can Word display full path of a file in the title bar? | Microsoft Word Help | |||
Formula to Sum values in word table | Mailmerge | |||
Underscore (_) will not always display in RTF files (Word 2002). | Microsoft Word Help | |||
Envelope Address | New Users |