0

If Statement and NL rows Function

Matilda Lee

August 29, 2017 22:28

None

Follow

Hi

I want to get the data using NL Rows function from the table that include emply field (e.g. list of job code in GL Entry table where Job code could be blank)

I used if statement as below and it only give me the first record not the list of records I wanted.

=IF(NL("ROWS",NP("UNION",NL("FILTER","G/L ENTRY","JOB CODE",....... FILTERS......)="","''",NL("ROWS",NO("UNION",NL("FILTER","G/L ENTRY","JOB CODE",............FILTERS.........))))

(Filters in above fomular are g/l account no., posting date and cost centre)

How can I make this work?

Thanks in advance.

 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis August 29, 2017 23:24

  Hi Matilda.

  While (as you have found) it is not possible to embed Jet's replicating functions [NL(Rows), NL(Columns), and NL(Sheets)] within an Excel IF() function, you can still perform conditional replication.

  The process for doing this is described in the Conditional Replication article in the Jet online knowledgebase.

• 0
  
  Matilda Lee August 29, 2017 23:35

  I simply want it to display '' when the field is blank as the NL function gives me an error message for empty filter. Conditional replication link you sent me explains when conditions exists outside of NL function (if cell C4=42). In my case, coditions exists dependent on the NL function result.

  e.g. if (NL(.......)= "","''",yyyyy)

  Can you please re-write this example for me.

   

  Thanks

   

• 0
  
  Harry Lewis August 30, 2017 14:03

  Hi Matilda -

  Probably what you would want to do is include an NL(Count) function to figure out how many records match your criteria, and then base your conditional replication on that count.

  Possible something like this:

  

  Edited by Harry Lewis August 31, 2017 13:58