0

NL(Sheets) error from options

Graham Hacking

May 08, 2019 15:52

Answered

Follow

I'm trying to create a Sheets function that will create a new sheet based on Subcontractor Valuations selected from a list on the options tab. When I run the report with an * in the option, it will produce a new sheet for all available values, however when I try to select 1 or multiple values, the sheets function returns a blank cell.

=NL("sheets","Subcontractor Ledger Entry","Closed by Entry No.","Company=",$C$3,"Job No.",$C$4,"Subcontractor",$C$6,"Valuation No.",$C$7,"Posting Date",$C$5)

any ideas?

Thanks, Graham

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Harry Lewis May 08, 2019 16:45

  Hello Graham.

  If you ensure that the options are on a separate worksheet (e.g., "Options") from the NL() function...

  Does this function produce the values you expect?

  =NL("Rows","Subcontractor Ledger Entry","Closed by Entry No.","Company=",Options!$C$3,"Job No.",Options!$C$4,"Subcontractor",Options!$C$6,"Valuation No.",Options!$C$7,"Posting Date",Options!$C$5)

• 0
  
  Graham Hacking May 09, 2019 07:56

  Hi Harry,

  No unfortunately this doesn't solve the issue.

• 0
  
  Harry Lewis May 09, 2019 14:19

  Thanks Graham.

  From that, I assume that the function does *not* return the values you are expecting.  This would indicate that NAV is not finding any records that match your criteria.

  Assuming that you are using NAV 2013 or higher, you could use the NAV Development Environment to check the data in the Subcontractor Ledger Entry table.  For the company listed in cell C3, find records where all of the following are true:

  Job No. is set to the contents of cell C4

  Subcontractor is set to the contents of cell C6

  Valuation No. is set to the contents of cell C7

  Posting Date is set to (or contained within) the contents of cell C5

   

  Another option would be to use Jet to query your date.

  Start with a function such as:

  NL("Rows","Subcontractor Ledger Entry","Closed by Entry No.","Company=",Options!$C$3)

  and check that it returns what you would expect.

  Slowly add your other parameters:

  NL("Rows","Subcontractor Ledger Entry","Closed by Entry No.","Company=",Options!$C$3,"Job No.",Options!$C$4)

  etc.

  to find out where the issue lies in the data.

   

   

   

  Edited by Harry Lewis May 09, 2019 14:29
• 0
  
  Graham Hacking May 09, 2019 15:06

  Harry,

  All sorted now, hadn't set one of the parameters correctly.

  Thanks