0

Pivot table shows #Value

Jet Reports Historic Posts

November 11, 2011 13:56

None

Follow

The user has a jetreport with a sheet where NL and NF functions retrieve values from the G/L entries (Blad 7).

In This sheet, the revenue entries are retrieved from the G/L entries (filter on set of G/L accounts) and based on the source no. the country code is retrieved from the customerpage.

In another sheet, a pivottable is created to have subtotals per countrycode. This pivot table updates itself, but provides a line with #Value.
On the line with #Value the amount of countrycode NL is displayed, where the amount on Countrycode NL is left empty.

the file:Verkoopoverzicht_per+dag_Def is with data, the other is the design version. I cannot share that on the community, but I have it.

• Kopie van Verkoopoverzicht_per dag_Def.xlsx

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Jet Reports Historic Posts November 11, 2011 16:07

  Hmm, it looks like most likely it is #VALUE because there is 1 or more #VALUE rows in the "Land" column (on the Blad7 sheet). The only reason Land would be #VALUE is because the value it is referencing column F is #VALUE. The most likely reason that a row in column F would be #VALUE is that the value for Klantnr in column E is blank.
  
  So the way I would go about fixing this would actually to be to make a change to your formula in column F to allow blank values in your filter. You can do this by prefixing the filter value with "@@" like the following:
  
  =NL("Rijen","Klant",,"Nr.","@@"&$E5)
  This way, you should not get a #VALUE in column F when column E is blank and then you should not get a #VALUE in the "Land" column. Does this work for you?
  
  Regards,
  Hughes