Overview
Sometimes you want to filter an NL function based on the contents of another cell or the output of another NL function, where either of which could be a blank value.
The NL function does not allow you to use blank filters, and you will get a #VALUE error message if you fill in a FilterField parameter and leave the corresponding filter blank.
When you are using a cell reference to filter on a blank value, you just need to change the syntax a little.
Any time you are going to reference a cell that has special characters or could be blank, simply put "@@" in front of the cell reference.
Example...
let's assume that we want to use the contents of cell D5 in our filter - but the contents of that cell could be blank. Here's how:
=GL(,$C10,,Option!$D$9,,"@@"&$D$5,,,,,,Option!$D$14)When D5 is blank, the function will use "@@", instead. This value is interpreted by your datasource to mean "blank".
Comments