0

Scheduler, Removed Lines, and Excel Formulas

Jet Reports Historic Posts

April 10, 2015 13:34

None

Follow

I believe the EVAL function will work unless you are doing this with a replicator function. If you are doing a rows replicator function and you use EVAL I don't know of a way to get the EVAL to use relative reference (i.e. If column "F" is my customer number rows replicator and adding columns "H" and "G" for a total in column "I" using =NP("Eval","=H6+G6") then this same function will appear in every row and not update).

Hello Jason, you are correct, if you did the command as you are showing, it would not work properly with a ROWS command, but all that is required is to use extra quotes to allow the formula to update as the rows are replicating.

so your formula needs to be =NP("EVAL","="""&H6&"""+"""&G6&"""") so on line 6, you will reference H6+G6
But when the formula moves down it becomes =NP("EVAL","="""&H7&"""+"""&G7&"""")

The trick here is that the cell references look like this (I've added spaces to make this more readable) " = " " " & H 7 & " " " + " " " & G 7 & " " " " - each reference is 'captured' in a series of three quotes and ampersand (&) which is reversed to close the cell reference.
Note that there are FOUR quotes at the end of the statement, because that closes the opening quotation mark before the equals sign (I've added color to demonstrate).

However, I do like RMW's solution, and I'll have to remember that when I'm needing to work with more complex formulas.

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts April 10, 2015 13:34

  Hi,
  
  I'm new to the forum, so hopefully I'm in the right place, and I don't duplicate the issue. If so, my apologies.
  
  Using the Jet Scheduler, you can have hidden rows, columns and sheets removed from the output file. That can be very convenient if your file contains lots of redundant help info. In my case, listings of Sales Shipment Lines, calculating the total sales amount in that Shipment, since there is no total amount in the header, not in the lines.
  After generating the file, I do not need these lines anymore, and may be deleted from the output file.
  
  But doing so, Excel cannot use them in it's formulas anymore, simply because they are not there anymore. The result is errors in the formulas and an unusable worksheet.
  
  The "+Values" option in cel A1 converts Jet formulas to values, but not Excel formulas.
  
  Does anyone know how to solve this?
  
  (Using the latest Jet Essentials 2015 update)
  
  Thanks!
  Koen.

• 0
  
  Jet Reports Historic Posts April 10, 2015 16:12

  The use of the NP(EVAL formula fixes this issue.
  
  For example, I am now working on a dashboard that needs to give me a percentage of sales total - but as a fixed value (not an excel formula that could be changed as rows move).
  So, I did the calculation in a hidden column, and then pulled the value into my report using NP(EVAL)
  
  in hidden column - I have total sales for a customer divided by the category sales (giving me percentage of the total sales) - this formula is handled in cell C16
  in my report, the EVAL formula is NP("EVAL","=C16")
  
  NOTE: the above works because I am returning a number value, if I were returning a TEXT value, EVAL seems to require that I wrap the cell reference in the extra quotes so it will move down the page as the ROWs command expands
  NP("EVAL","="""&C16&"""") after the equals sign I have three quotes and an ampersand (&), and following the cell reference it is and ampersand followed by FOUR quotes - to close the original quotes in front of the equals sign
  

• 0
  
  Jet Reports Historic Posts April 12, 2015 12:03

  I usually add '&NP("Eval","=""""")' to an Excel formula that produces text as a result and '*NP("Eval","1")' to a formula that has a number result.
  As soon as there is any Jet in the formula it will be converted to values.
  
  HTH
  
  rmw

• 0
  
  Jet Reports Historic Posts April 13, 2015 18:18

  Hi Heather.
  
  I believe the EVAL function will work unless you are doing this with a replicator function. If you are doing a rows replicator function and you use EVAL I don't know of a way to get the EVAL to use relative reference (i.e. If column "F" is my customer number rows replicator and adding columns "H" and "G" for a total in column "I" using =NP("Eval","=H6+G6") then this same function will appear in every row and not update).
  
  Another way to do this may be to add the functions together into the same cell and eliminate the hidden columns. If you are trying to sum the info from multiple tables instead of having multiple functions and then deleting the rows you could just add the functions in the same cell.
  Example of summing the sales invoice lines and sales cr. memo lines in the same cell: "=NL("Sum","Sales Invoice Line","Amount","Document No.",E18)-NL("Sum","Sales Cr.Memo Line","Amount","Document No.",E18)"
  
  If someone knows how to use Eval with a rows function then please update the ticket and share.
  
  Thanks
  
  Jason

• 0
  
  Jet Reports Historic Posts April 14, 2015 19:21

  …i.e. If column "F" is my customer number rows replicator and adding columns "H" and "G" for a total in column "I" using =NP("Eval","=H6+G6") then this same function will appear in every row and not update…

  
  That is correct and as it should be, because the original use of NP("Eval") is to prevent recalculation.
  
  But what if you tried =H6+G6*NP("Integers","1","1") ?
  
  That way the calculation is still relative and recalculated but at the end of the report the formula is converted to a value, because there is a Jet function in it.
  
  HTH
  
  rmw