0

Return All Entries NOT Equal to a Filter?

Hi All,

I’m having some trouble creating a report which returns all entry no.’s not included in a NL Filter of entry numbers. This seemed like a simple task to me at first as I assumed I could write a rows function to return all Entry No which are not equal to the filter. But apparently this is not allowed as I am given a #Value message when I try to say “<>”&FilterCellReference. Why not just filter for what you do want? Well, here’s the story.

I have written a report with two sections. The first section contains multiple lines, each line containing a Sum Function that is filtered by two unique criteria, the G/L Account No. field and the Global Dimension 01 field. Each line has a unique combination of G/L numbers and Global Dimension values although a single G/L number or Global Dimension Value could be repeated in each line. What makes each line unique is the combination of the two field values. The Sum Function returns the sum of all entry amounts. Simple enough.

Example:
Line 1: GD: 01|03|05 G/L #: 1501|5070..5999
Line 2: GD: 02|04 G/L #: 5060|5075
Line 3: GD: 01..05 G/L #: 6000…8999
Line…(too many more to list)

But now I want to see what I’m missing that is not being picked up by all lines in the first section of the report. So I create a second section to list all entry numbers not included in the first sections results and began to run into problems.

My first idea was to put a filter on each line of the report. This filter would return all entry no.’s used in that line and all I would need to do is write a row function returning all entry no.’s not used in any line. But I can’t seem to use something that says NOT EQUAL TO THE FILTER. I’ve tried syntax line “<>”&FilterCellReference, but each time it just gives me back #Value. I have also tried using a difference filter (I think I'm using it correctly). There are too many combination of G/L and GD values to create a last line withing all of the remaining possible permutations. It would just be a nightmare.

If anyone has any ideas they can give I’d really appreciate the suggestions! Thanks in advance!

8 comments

Please sign in to leave a comment.