0

NL Filter with multiple filter values (NL Rows different than NL Filter?)

Tom

July 11, 2019 11:52

None

Follow

Hi,

 

I'm using the following NL filter formula to filter the GL accounts linked to various consolidation codes. Column A contains the consolidation codes. I use the following formula:

=NL("Filter";"G/L Account";"No.";"Consol. Debit Acc.";A1)

This works perfect for cell A1, which has code 80000. 

However, cell A2 contains the codes 80001..800010  The formula doesn't work now, it retrieves the 1st value linked to the 1st code, in this case it is the 1st GL account linked to 80001. 

I tried replacing Filter for Rows by =NL("Rows";"G/L Account";"No.";"Consol. Debit Acc.";"83000..86011")

and this works well. Any idea what's the difference between Rows and Filter? Thx

 

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Harry Lewis July 15, 2019 14:52

  Hello Tom -

  First, remember (in a Jet report) column A and row 1 are reserved for use by the Jet add-in.  This is so that, when the report is run (which is required to get accurate results - rather than the sample results that are used when the report is in Design mode), Jet has all the information it needs to create the report.

  Other than that, I am not able to duplicate what you describe.

  Since my sample database has no data in the Consol. Debit Acc field, I used the Indentation field (being sure not to use column A or row 1):

  

   

  When I run the report, here are the results I get:

  

  Cell C9 contains all the GL account numbers with an Indentation of 1.

  Cell C10 contains all the GL account numbers with an Indentation of 2 through 3.

  Cell C11 contains all the GL account numbers with an Indentation of 4.

  Does this method work for you?

  Edited by Harry Lewis July 15, 2019 14:52