Hi All,
I'm sure this is simple, but I just can't get it right.
I have several ranges of GL Numbers.
Example:
Cell A1 = 1000..1005|1110..1112
Cell A2 = 4000..4025|4035..4099
I want to write a NL rows function which will return all G/L Numbers not included in either of these ranges. I have tried the following without success.=NL("Rows","G/L Account","No.","No.","<>A1|<>A2")=NL("Rows","G/L Account","No.","No.","<>A1&<>A2")
I have also tried creating a NL filter in A3 using cells A1 and A2, but that has also failed to return the correct results.=NL("Filter","G/L Account","No.","No.","<>"&A3)
Can someone suggest what I'm doing wrong and how to create the needed NL function?
Thanks in advance for any ideas!
Date
Votes
1 comment
-
Jet Reports Historic Posts Hello!
This is possible in a couple different ways. The best way is to redefine your filter so that you are filtering for what you WANT rather than what you don't want. So you could make it something like this:
..999|1005..1109|1113..3999|4026..4034|4100..
That will be the best and fastest way to do this, which I would recommend. The other way to do it would be to use NP(Difference) like this:
C2: =NL("Filter","G/L Account","No.")
C3: =NL("Filter","G/L Account","No.","No.",A1&"|"&A2)
C4: =NL("Rows",NP("Difference",$C$2,$C$3))
Does one of these work for you?
Regards,
Hughes
Please sign in to leave a comment.