Djeeni Formulas

One of the most powerful concepts of MS Excel is the formula. You can enter a fomula like this:

  =SUM(B4:D5)+AVERAGE(F6:F9)

into a cell and Excel makes the calculation. In Djeeni you can use all MS Excel formulas including all available functions. But Djeeni extends further the capabilities of formulas to make your process even more powerful. Read further to see how.

Djeeni formula extensions overview

To enable the Djeeni extensions within MS Excel formulas Djeeni uses the […..] syntax. That means you have to enclose most of the Djeeni formulas in the square brackets. See the sections below the overview table for detailed explanations.

Djeeni formula extensions overview
To refer to a Type this MS Excel Example Djeeni Example
Cell [$…] D4, Worksheet!D4, [Path\To\Workbook]Worksheet!D4 [$wsName!D4]
Range [$…] D4:E5 [$wsName!D4:E5]
Cell Value [=…] value of the cell D4 [=wsName!D4]
Named range [:…] namedRange [:namedRange]
Cell reference of a located value [# … |cell] - [#ceValue|cell]
Row number of a located value [# … |row] - [#ceValue|row]
Column letter of a located value [# … |column] - [#ceValue|column]
Last column with data (in the first row) [#ColumnEnd] or #ColumnEnd - [$wsName![#ColumnEnd]2]
Last column with data in a specific row [#ColumnEnd|Row] - [$wsName![#ColumnEnd|2]2]
Last row with data (in column A) [#RowEnd] or #RowEnd - [$wsName!C#RowEnd]
Last row with data in a specific column [#RowEnd|Column] - [$wsName!C#RowEnd|C]
Current row/column of the innermost Row/Column List [#] or # - [$wsName!F#]
Current row/column of a specified Row/Column List [#|DjeeniName] - [$wsName!F[#|rlRowList]]
Current worksheet in a workbook list Djeeni name of the list - [$wlName!B5]
Workbook folder, filename and file extension of a worksheet [#worksheet|Folder], [#worksheet|File], [#worksheet|Extension] - [#wsName|Folder], [#wsName|File], [#wsName|Extension]
Excel name of a worksheet [#worksheet|Name] - [#wsNamee|Name]
Calculations with values [+calculation] - [+([#RowEnd]-1) * ([#ColumnEnd]-2)]
Folder of the Djeeni client (relative folder) [#DjeeniFolder] or #DjeeniFolder - #DjeeniFolder\finance\reports

Refer to a cell or range

In an Excel formula, you can refer to a cell on the current worksheet by giving its column letter and row number:

  B4

Ranges are referred by specifying the top-left and bottom-right cells:

  C5:F8

To be clear in your process, Djeeni requires you to refer to cells or ranges with their worksheets where worksheets are referred by their Djeeni names:

  [$wsExample!B3:T45]

where [$…] is the cell reference part of the formula.

Djeeni formula extensions within an Excel formula

In MS Excel, you enter a formula in a cell or a range of cells by typing = and the formula in them. For example =SUM(B3:B6). In Djeeni you do the same using the Cell Set or Range Set process steps:

  =SUM([$wsExample!B3:B6])

You can use multiple Djeeni formula extensions within an Excel formula:

  =IF([$wsExample!B4]>5,[$wlSource!C#],"N/A")

Named ranges

You can name a range using the Range Use process step by giving a Djeeni name to your range. Further, you can refer to your named range in formulas as [:yourNamedRange]:

  =SUM([:yourNamedRange])

Located values

After a value is located in a range using the Locate Value process step it is possible to refer to the found cell. Let's suppose that Locate Value saves its result under Djeeni Name ceValue. Then

  #ceValue|cell                  'reference to the found value

refers to the cell of the found value. Example: if Locate Value finds the value in cell C4 on the worksheet wsSource then [#ceValue|cell] is equal to [$wsSource!C4] .

It is also possible to use the row number and the column letter of the cell where the value is found:

  #ceValue|row                      'row number of the cell with the found value
  #ceValue|column                   'column letter of the cell with the found value

Example: if Locate Value finds the value in cell C4 on the worksheet wsSource then [#ceValue|row] is 4 while [#ceValue|column] is C.

Last row in a column

You can find the last row in column A by typing [#RowEnd]. If you are inside a Djeeni formula extension you may leave [] out and type simply #RowEnd:

  [$wsExample!C#RowEnd]    'the cell in Column C that is in the row of the last cell in column A

You can refer to the last row of any column by typing [#RowEnd|ColumnLetter]:

  [$wsExample!C[#RowEnd|B]]    'the cell in column C that is next to the last cell in column B

Last column in a row

You can find the last column in row 1 by typing [#ColumnEnd]. If you are inside a Djeeni formula extension you may leave [] out and type simply #ColumnEnd:

  [$wsExample!#ColumnEnd3]    'the cell in row 3 that is under the last cell in row 1

You can refer to the last column of any row by typing [#ColumnEnd|RowNumber]:

  [$wsExample![#ColumnEnd|4]3]    'the cell in row 3 that is above the last cell in row 4

Current row in Row/Column Lists

Within a Row List or a Column List loop you can refer to the current row of the worksheet by typing [#]. If you are inside a Djeeni formula extension you may leave [] out and type simply #:

  [$wsExample!G#]       'the cell in column G and the current row of the row list

You can use [#] to refer to cells on other worksheets and embed the current row number in calculations. Example: you want to copy values from row 2 on a source worksheet to row 5 on a target worksheet then from row 3 to row 6 and so on. You can type into the Range Copy/Move To process step:

  From range: $wsSource!A#:D#
  Target cell: $wsTarget!A[+#+3]

Nesting

If you are nesting 2 or more Row or Column Lists within one another, using [#] will return a current row/column of the innermost loop. To get the current row/column of one of the outer loops, use the djeeni name of the desired row/column list as a parameter next to # character enclosed in angular bracket

[#|rlRowListDjeeniName]    'to specify outer Row Lists 

or

[#|clColumnListDjeeniName]    'to specify outer Column Lists

Business Example: Let's say you want to iterate through all the rows in your dataset - outer Row List and for each row through all the columns - inner Column List. To reference a current cell in for example a Cell Set, you would need to type in the following Djeeni formula:

wsExample![#|rlRowListDjeeniName][#|clColumnListDjeeniName]   'Current cell reference to use in the Cell parameter of a Cell set

or

wsExample![#|rlRowListDjeeniName]#    'we can leave out the |clColumnListDjeeniName part and square brackets

Calculations

Moving data from one location to another involves calculations. In Djeeni you can use the [+…] formula extension to perform calculations.

Example calculations
Expected result Djeeni formula
The row below the last row with value [+#RowEnd+1]
The column right to the last column with data [+#ColumnEnd+1]
Create three target rows for every source row in a row list [+#*3], [+#*3+1], [+#*3+2]
Create a summary value of every 100 rows in a row list SUM(A[+#-99]:A[#]) ⇒ B[+#/100]

Relative folder specification

To use a relative location you can use the #DjeeniFolder reserved name, which will take the folder where your djeeni client file is currently saved. From there you can then specify the subfolders, if needed.