If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to suppress an entire row in a table, if no data?
Q: how to suppress an entire row in a table, if the first row or column cell is blank? Background: 1) I am doing a mail merge will Excel 2007. 2) Excel data base: 100 clients each in a row with corresponding data by year in each column. 3) Problem: I am sending a letter to each client with their data in a table. Some clients have been with us for 10 years some have not, so the first couple of rows in the table for some clients may be blank, how do it suppress these rows? |
#2
|
|||
|
|||
How to suppress an entire row in a table, if no data?
Lightjag
You can't do that directly with a mailmerge. You could, however, have 10 consecutive 1-row tables and use an IF test to determine whether a particular table should be displayed. If you set the paragraph that necessarily separates these table to 1pt high, they'll look much the same as a single table. Alternatively, you could have have 10 consecutive tables with increasing row counts and use an IF test to determine which of those tables should be displayed. In either case, you put the entire table within the corresponding IF test. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Q: how to suppress an entire row in a table, if the first row or column cell is blank? Background: 1) I am doing a mail merge will Excel 2007. 2) Excel data base: 100 clients each in a row with corresponding data by year in each column. 3) Problem: I am sending a letter to each client with their data in a table. Some clients have been with us for 10 years some have not, so the first couple of rows in the table for some clients may be blank, how do it suppress these rows? |
#3
|
|||
|
|||
How to suppress an entire row in a table, if no data?
Thanks! but what would the IF test look like?
ex/ Client 1 - "Table 1: approx 5 individual tables" Year Return 2003 N/A = suppress this row 2004 N/A = suppress this row 2005 10% 2006 8% 2007 7% Note: I can have the year and return columns pull blanks (i.e. "") from excel, but I still need to suppress the blank rows. Thanks in advance for help in writing the "IF Test". "macropod" wrote: Lightjag You can't do that directly with a mailmerge. You could, however, have 10 consecutive 1-row tables and use an IF test to determine whether a particular table should be displayed. If you set the paragraph that necessarily separates these table to 1pt high, they'll look much the same as a single table. Alternatively, you could have have 10 consecutive tables with increasing row counts and use an IF test to determine which of those tables should be displayed. In either case, you put the entire table within the corresponding IF test. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Q: how to suppress an entire row in a table, if the first row or column cell is blank? Background: 1) I am doing a mail merge will Excel 2007. 2) Excel data base: 100 clients each in a row with corresponding data by year in each column. 3) Problem: I am sending a letter to each client with their data in a table. Some clients have been with us for 10 years some have not, so the first couple of rows in the table for some clients may be blank, how do it suppress these rows? |
#4
|
|||
|
|||
How to suppress an entire row in a table, if no data?
Hi Lightjag,
Basically, its just a set of simple IF tests, coded along the lines of: {IF{MERGEFIELD 2003Val}= "N/A" "" "Table Row with {MERGEFIELD 2003Val}"} {IF{MERGEFIELD 2004Val}= "N/A" "" "Table Row with {MERGEFIELD 2004Val}"} {IF{MERGEFIELD 2005Val}= "N/A" "" "Table Row with {MERGEFIELD 2005Val}"} {IF{MERGEFIELD 2006Val}= "N/A" "" "Table Row with {MERGEFIELD 2006Val}"} {IF{MERGEFIELD 2007Val}= "N/A" "" "Table Row with {MERGEFIELD 2007Val}"} To create and run a mailmerge with one such row, you'd: .. make a one-line table containing the mergefields for a single year. .. select the and press Ctrl-F9 to wrap it in a field .. reposition the cursor to the right of the first field brace '{' and press Ctrl-F9 again the insert a second field inside the first, ahead of the table. .. fill in the coding as indicated, replacing '2003Val' with the name of the field that returns 'N/A' when there's nothing for the year (you can alos omit 'Table Row with '). .. press F9 to update the field .. run your merge. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Thanks! but what would the IF test look like? ex/ Client 1 - "Table 1: approx 5 individual tables" Year Return 2003 N/A = suppress this row 2004 N/A = suppress this row 2005 10% 2006 8% 2007 7% Note: I can have the year and return columns pull blanks (i.e. "") from excel, but I still need to suppress the blank rows. Thanks in advance for help in writing the "IF Test". "macropod" wrote: Lightjag You can't do that directly with a mailmerge. You could, however, have 10 consecutive 1-row tables and use an IF test to determine whether a particular table should be displayed. If you set the paragraph that necessarily separates these table to 1pt high, they'll look much the same as a single table. Alternatively, you could have have 10 consecutive tables with increasing row counts and use an IF test to determine which of those tables should be displayed. In either case, you put the entire table within the corresponding IF test. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Q: how to suppress an entire row in a table, if the first row or column cell is blank? Background: 1) I am doing a mail merge will Excel 2007. 2) Excel data base: 100 clients each in a row with corresponding data by year in each column. 3) Problem: I am sending a letter to each client with their data in a table. Some clients have been with us for 10 years some have not, so the first couple of rows in the table for some clients may be blank, how do it suppress these rows? |
#5
|
|||
|
|||
How to suppress an entire row in a table, if no data?
A couple of questions:
1) what do you mean " ahead of the table" ? I have put the IF test within the row (i.e. the 1 row table that is pulling the return). 2) I don't think Word is recognizing the IF TEST, as the row still appears when there is no data. "macropod" wrote: Hi Lightjag, Basically, its just a set of simple IF tests, coded along the lines of: {IF{MERGEFIELD 2003Val}= "N/A" "" "Table Row with {MERGEFIELD 2003Val}"} {IF{MERGEFIELD 2004Val}= "N/A" "" "Table Row with {MERGEFIELD 2004Val}"} {IF{MERGEFIELD 2005Val}= "N/A" "" "Table Row with {MERGEFIELD 2005Val}"} {IF{MERGEFIELD 2006Val}= "N/A" "" "Table Row with {MERGEFIELD 2006Val}"} {IF{MERGEFIELD 2007Val}= "N/A" "" "Table Row with {MERGEFIELD 2007Val}"} To create and run a mailmerge with one such row, you'd: .. make a one-line table containing the mergefields for a single year. .. select the and press Ctrl-F9 to wrap it in a field .. reposition the cursor to the right of the first field brace '{' and press Ctrl-F9 again the insert a second field inside the first, ahead of the table. .. fill in the coding as indicated, replacing '2003Val' with the name of the field that returns 'N/A' when there's nothing for the year (you can alos omit 'Table Row with '). .. press F9 to update the field .. run your merge. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Thanks! but what would the IF test look like? ex/ Client 1 - "Table 1: approx 5 individual tables" Year Return 2003 N/A = suppress this row 2004 N/A = suppress this row 2005 10% 2006 8% 2007 7% Note: I can have the year and return columns pull blanks (i.e. "") from excel, but I still need to suppress the blank rows. Thanks in advance for help in writing the "IF Test". "macropod" wrote: Lightjag You can't do that directly with a mailmerge. You could, however, have 10 consecutive 1-row tables and use an IF test to determine whether a particular table should be displayed. If you set the paragraph that necessarily separates these table to 1pt high, they'll look much the same as a single table. Alternatively, you could have have 10 consecutive tables with increasing row counts and use an IF test to determine which of those tables should be displayed. In either case, you put the entire table within the corresponding IF test. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Q: how to suppress an entire row in a table, if the first row or column cell is blank? Background: 1) I am doing a mail merge will Excel 2007. 2) Excel data base: 100 clients each in a row with corresponding data by year in each column. 3) Problem: I am sending a letter to each client with their data in a table. Some clients have been with us for 10 years some have not, so the first couple of rows in the table for some clients may be blank, how do it suppress these rows? |
#6
|
|||
|
|||
How to suppress an entire row in a table, if no data?
Hi Lightjag,
The problem is that you're putting the IF test inside a cell, whereas you need to put the whole table inside the IF test. If you follow my instructions about selecting the *table* and pressing Ctrl-F9, you'll see what I mean. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... A couple of questions: 1) what do you mean " ahead of the table" ? I have put the IF test within the row (i.e. the 1 row table that is pulling the return). 2) I don't think Word is recognizing the IF TEST, as the row still appears when there is no data. "macropod" wrote: Hi Lightjag, Basically, its just a set of simple IF tests, coded along the lines of: {IF{MERGEFIELD 2003Val}= "N/A" "" "Table Row with {MERGEFIELD 2003Val}"} {IF{MERGEFIELD 2004Val}= "N/A" "" "Table Row with {MERGEFIELD 2004Val}"} {IF{MERGEFIELD 2005Val}= "N/A" "" "Table Row with {MERGEFIELD 2005Val}"} {IF{MERGEFIELD 2006Val}= "N/A" "" "Table Row with {MERGEFIELD 2006Val}"} {IF{MERGEFIELD 2007Val}= "N/A" "" "Table Row with {MERGEFIELD 2007Val}"} To create and run a mailmerge with one such row, you'd: .. make a one-line table containing the mergefields for a single year. .. select the and press Ctrl-F9 to wrap it in a field .. reposition the cursor to the right of the first field brace '{' and press Ctrl-F9 again the insert a second field inside the first, ahead of the table. .. fill in the coding as indicated, replacing '2003Val' with the name of the field that returns 'N/A' when there's nothing for the year (you can alos omit 'Table Row with '). .. press F9 to update the field .. run your merge. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Thanks! but what would the IF test look like? ex/ Client 1 - "Table 1: approx 5 individual tables" Year Return 2003 N/A = suppress this row 2004 N/A = suppress this row 2005 10% 2006 8% 2007 7% Note: I can have the year and return columns pull blanks (i.e. "") from excel, but I still need to suppress the blank rows. Thanks in advance for help in writing the "IF Test". "macropod" wrote: Lightjag You can't do that directly with a mailmerge. You could, however, have 10 consecutive 1-row tables and use an IF test to determine whether a particular table should be displayed. If you set the paragraph that necessarily separates these table to 1pt high, they'll look much the same as a single table. Alternatively, you could have have 10 consecutive tables with increasing row counts and use an IF test to determine which of those tables should be displayed. In either case, you put the entire table within the corresponding IF test. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Lightjag" wrote in message ... Q: how to suppress an entire row in a table, if the first row or column cell is blank? Background: 1) I am doing a mail merge will Excel 2007. 2) Excel data base: 100 clients each in a row with corresponding data by year in each column. 3) Problem: I am sending a letter to each client with their data in a table. Some clients have been with us for 10 years some have not, so the first couple of rows in the table for some clients may be blank, how do it suppress these rows? |
Thread Tools | |
Display Modes | |
|
|