0

I've got a particular group of mergefields that I'm trying to colour using conditional formatting.

Basically, this column with the table is a percentage and I need it coloured red if it's below zero and green if it's above zero. However, as this percentage was not available for every entry, it can also appear as "No data", which I would like coloured in black.

Unfortunately, "No data" ends up in green every time. I'm following the instructions from this previously asked question, but I guess mine might be a bit different.

I would appreciate if someone could help me with this. I'm using MS Word 2016, if that makes a difference. Thanks in advance.

NickB
  • 3
  • 1
  • Yes. This can be done. The simplest is an IF field for each condition since they are mutually exclusive. What do you want if your merge field contains zero? (You want to plan for all possible conditions.) Or is zero no data? Conditional formatting is much simpler in Excel! – Charles Kenyon Jul 27 '21 at 02:28
  • @CharlesKenyon Thanks for the response. The chance of a zero is very unlikely, and in most cases it would be "No data". I have tried creating an IF field for each condition but it never seemed to work. In a nutshell, I used { IF { MERGEFEILD field } > 0 { MERGEFIELD field \* Charformat } **}, and then added the next IF fields where the double astericks are. – NickB Jul 27 '21 at 06:11

1 Answers1

0

You can use two independent IF Fields since there is no overlap.

Screenshot of field

You can convert the text shown below into a pair of fields using Graham Mayor's utility.

{ IF { MERGEFIELD Field } < 0 "{ MERGEFIELD Field \* CharFormat}" "" }{ IF { MERGEFIELD Field } > 0 "{ MERGEFIELD Field \* CharFormat }" "" }

You will have to add the color yourself. (The second set of quotation marks in each IF field is not really required.)

Here is my article on the Microsoft site on dealing with fields.

If there is overlap, you will need one or more nested IF fields. These are more complex, with one IF field inside another. A second IF field acts as the falsetext part of the first field. Here is my article on one way of constructing nested IF fields.

Charles Kenyon
  • 4,283
  • 2
  • 8
  • 19
  • Hi Charles. Many thanks for this. I would appear I made an error in nesting the IFs. I have tried your solution, which is much cleaner than mine, however the "No data" is still following the same rule of the last IF. I'm assuming the above means "If above 0, Green, if not, black" and "If below 0, Red, if not, black". – NickB Jul 28 '21 at 15:04
  • Does it actually say "No data" in your field? If so, that would be > 0 and require a nested IF field. – Charles Kenyon Jul 28 '21 at 16:26
  • Hi Charles, yes it does. Well, that explains why it's coming out in the 'above 0' colour. I'm assuming that I need to nest an "{ IF { MERGEFIELD Field } = No data {MERGEFIELD FIeld \* Charformat } } within the other two IF statements, but I think I've only jumbled it up even more... – NickB Jul 28 '21 at 16:52
  • Update: I've gotten closer to the solution where the colors obey the rules, but when it comes to producing a black output for 'No data', it seems to be printed twice. For what it's worth, I am appending the statement here. Apologies if it's messy. ```{ IF { MERGEFIELD Field } < 0 "{ MERGEFIELD Field \* CharFormat}" "{ IF { MERGEFIELD Field } = No data "{ MERGEFIELD Field \* CharFormat} }" }{ IF { MERGEFIELD Field } > 0 "{ MERGEFIELD Field \* CharFormat }" "{IF { MERGEFIELD Field } = No data "{ MERGEFIELD Field \* CharFormat}" ""}" }``` – NickB Jul 28 '21 at 17:17
  • See my article on constructing nested IF fields. https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_win10-mso_365hp/nested-if-fields-a-method-to-create-them/1ebb072e-92cd-4e8a-9429-03eba072469c *(I've added the link to the answer above, as well.)* – Charles Kenyon Jul 29 '21 at 15:49
  • Hi Charles. Thank you so much for your help and your article. After many attempts I was able to find what I needed: ```{ IF { MERGEFIELD General_Manager_var } = "No data" { MERGEFIELD Field \* Charformat } { IF { MERGEFIELD Field } < 0 { MERGEFIELD Field \*Charformat } { MERGEFIELD Field \* Charformat } } }```. Of course, the appropriate colors were added. – NickB Jul 29 '21 at 17:37