IBM i (AS400). Dynamic Excel functions available with iEXL

Example Function using OFFSET.

 

Example work file.

  

 

Notice the OFFSET value has been set to minus 1

The field OFFSET can be named anything you like.

The value of minus one has been entered in this example as fields Jan, Feb and Mar fields only contain positive values. The value minus one can be any value that is guaranteed not to appear in any other numeric field/cell. Only place minus one in the OFFSET field where you would like the function to be placed.




Create function within iEXL.

Enter: GO IDSMAIN.

Select option 1

Select option 5 against required spreadsheet.

Press F17.

When the function is displayed in a spreadsheet it may be as below

=A2*OFFSET(A2,1,0)+B2*OFFSET(B2,1,)+C2*OFFSET(C2,1,0)

In the above screen shot the specified row number of 2 is replaced by the constant ‘ROW’

Note the value ‘ROW’ appears multiple times within this function.

The ‘trigger start’ value of minus 1 has been entered to match the OFFSET field is your work file. This tells the system that if minus 1 is found in a field/cell the function should be applied. When this happens, the entered function is accessed and where the letters ‘ROW’ are found they will be replaced with the actual row being processed. This function is then applied to the cell within the spreadsheet. 

This makes the function dynamic as it is only defined once but applied to the spreadsheet as many times as required. 

Note the ‘Row/Col’ value is set to ‘R’



When the iEXL command is run the following spreadsheet is generated.


The above example uses the constant ‘ROW’ to create dynamic functions. The constant ‘COL’ can be used to create dynamic functions at column level.

Where required both ‘ROW’ and ‘COL’ constants can be used in conjunction. 

Saturday, March 14, 2020 8:24 AM