0

How to sum a column from an NL Table

Jet Reports Historic Posts

September 13, 2011 10:25

None

Follow

Hi All

I have been using the NL table function to pull data from the "sales line" table in NAV which is great.

The problem i have is i want to sum a colum of the table on a seperate sheet. In Report mode this works =SUM(Sales_Line[Outstanding Amount (LCY)]) But when you switch back to design mode you loose the formula and cant recreate it as the table doesnt exsist untill the report is run.

Just wondered if anyone has any ideas?

Thanks.

PS im on the latest 2011 version.

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Jet Reports Historic Posts September 14, 2011 20:24

  Try using SUMIF on the "summary sheet you can "Hard code the range to extend well past anything you expect to get
  
  
  example SUMIF(Sheet1 F1:F4000,"<>""",Sheet1G1:G4000)

• 0
  
  Jet Reports Historic Posts September 19, 2011 17:12

  Hi,
  
  You could do an NL(Sum) function like this:
  
  =NL("Sum","Sales Line","Outstanding Amount (LCY)",…)
  
  Then of course you would have to add whatever filters you are setting on your NL(Table) function. Another option would be to create a Pivot table which references the table created by your NL(Table) function. You could create a sum in the pivot table of the outstanding amount column. If your NL(Table) function has a TableName= filter to set the table name, then your pivot table can reference that table name and the reference will not be lost when you go between modes. Does this help?
  
  Regards,
  Hughes