May 25 2021 07:16 AM
I'm running a FILTER function to create the input data for a Word mail merge document. What value do I test for in the mail merge for a blank cell returned by the FILTER function?
If I add two fields into the mail merge document and one of them is blank in the source data the mail merge field displays a zero. If I do a conditional test (IF THEN ELSE) in the mail merge on the source data using blank (" ") or zero as the test, neither find the blank value. I'm assuming that, behind the scenes, Excel is populating a "blank" cell result from a FILTER function as some special character or value (not blank or zero) and I just need to test for that.
If I run the mail merge on the original (pre-FILTER) data it picks up the blank values correctly so I assume it's something about the way data is treated when processed by the FILTER function.
Anyone any ideas?
Thanks
May 25 2021 08:02 AM
FILTER() returns blanks as zero.
May 25 2021 09:05 AM
May 25 2021 11:36 AM
It sounds as if you're doing the IF THEN ELSE conditional test in Word. Which is fine; I've done the same on occasion. What I'm wondering is whether you could just (in Excel) change the values in those added cells--if I understood you again--to "Y" or "N" or something other than blank/zero. Since the value per se isn't apparently of significance in itself--not a part of an amount that needs to be calculated or some such--but is used to direct a custom sentence or paragraph--making it unmistakably "Y" or "N" might be an appropriate work-around.
May 27 2021 01:44 AM