0

Filtering a Linked NL Function

Jet Reports Historic Posts

September 04, 2013 21:13

None

Follow

Hello again,

Question - I have the following NL formula:

=NL("Rows","Country/Region","Name","Company=","Portland","Link=","EF Extended Field Entry","Code Value","=Code","Category Code","COUNTRY",,,"Company=","Portland")

This formula returns a list of 50 Countries. I am trying to narrow this list down to return only two countries of my choice. How would I include this type of filter? What would the filter field be? Would I use the or "Country 1 | Country 2" as the filter?

Thank you!

Tom

• Facebook
• Twitter
• LinkedIn

4 comments

• 
  Jet Reports Historic Posts September 05, 2013 13:28 Official comment

  Tom,
  
  You can't embed a replicator inside another formula. A replicator like NL(Rows) won't expand unless it is the first thing in the cell. All you need to do is put your =Proper() formula in the next cell to the right and reference the result of the NL(rows). Then you can put "Hide" in row 1 of the column holding the NL(Rows) formula and that column will be hidden when you run the report so you will only see the proper name. Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts September 04, 2013 23:04

  Hi Tom,
  
  Yes you would use an or filter just like you describe. So if you are trying to filter by the country name, then it would look like this:
  
  =NL("Rows","Country/Region","Name","Name","Country1|Country2","Company=","Portland","Link=","EF Extended Field Entry","Code Value","=Code","Category Code","COUNTRY",,,"Company=","Portland")
  Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts September 04, 2013 23:56

  Hi Hughes,
  
  Gives me a great working start but the effectiveness of it begged another question. Is there a way to layer on a bit of excel formatting that would return would return the proper capitalization?
  
  Here is an example:
  
  1. The records for the countries in the table are stored in all caps (i.e. JORDAN ).
  2. The format that my replicated list needs to take is: the first letter capitalized and all subsequent letters lowercase (i.e. Jordan).
  3. I found a natural excel formula (=Proper) that corrects this so I incorporated it into the formula. The formula now is:
  
  =PROPER(NL("Rows","Country/Region","Name","Name","JORDAN|UGANDA","Company=","Portland","Link=","EF Extended Field Entry","Code Value","=Code","Category Code","COUNTRY",,,"Company=","Portland"))
  
  I was expecting a replicated list of:
  Jordan
  Uganda
  
  All I received in my list was:
  Jordan
  
  The other country (Uganda) was left out.
  
  Maybe the "Proper" formula isn't compatible? When I take this formula out the list returns both countries in all caps.
  
  I appreciate the continued help!!
  
  Tom

• 0
  
  Jet Reports Historic Posts September 05, 2013 23:37

  Hi Hughes,
  
  Thank you. That helped a lot and solved my issue.
  
  I appreciate the continued help in developing these skills.
  
  Thanks!
  
  Tom