One of the most powerful concepts of MS Excel is the formula. You can enter a formula 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.
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 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 found cell | [# … |cell] | - | [#ceCell|cell] |
Row number of a found cell | [# … |row] | - | [#ceCell|row] |
Column letter of a found cell | [# … |column] | - | [#ceCell|column] |
Last column with data in row 1 | [#ColumnEnd] or #ColumnEnd | - | [$wsName![#ColumnEnd]2] |
Last column with data in a specific row | [#ColumnEnd|Row] | - | [$wsName![#ColumnEnd|2]2] |
First column after last with data in row 1 | [#ColumnNext] or #ColumnNext | - | [$wsName![#ColumnNext]2] |
First column after last one with data in a specific row | [#ColumnNext|Row] | - | [$wsName![#ColumnNext|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] |
First row after the last one with data in column A | [#RowNext] or #RowNext | - | [$wsName!C#RowNext] |
First row after the last one with data in a specific column | [#RowNext|Column] | - | [$wsName!C#RowNext|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] | - | [#wsName|Name] |
Calculations with values | [+calculation] | - | [+([#RowEnd]-1) * ([#ColumnEnd]-2)] |
Folder of the Djeeni client (relative folder) | [#DjeeniFolder] or #DjeeniFolder | - | #DjeeniFolder\finance\reports |
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.
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")
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])
After a value is looked up in a range using the Cell Lookup process step it is possible to refer to the found cell. Let's suppose that Cell Lookup 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 Cell Lookup 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 Cell Lookup finds the value in cell C4 on the worksheet wsSource then [#ceValue|row] is 4 while [#ceValue|column] is C.
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
You can find the next row after the last one with data in column A by typing [#RowNext]. If you are inside a Djeeni formula extension you may leave [] out and type simply #RowNext:
[$wsExample!C#RowNext] 'the cell in Column C that is in the row of the first empty cell in column A
You can refer to the first row after the last one with data of any column by typing [#RowNext|ColumnLetter]:
[$wsExample!C[#RowNext|B]] 'the cell in column C that is next to the first empty cell in column B
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
You can find the first column after the last one with data in row 1 by typing [#ColumnNext]. If you are inside a Djeeni formula extension you may leave [] out and type simply #ColumnNext:
[$wsExample!#ColumnNext3] 'the cell in row 3 that is under the first empty cell in row 1
You can refer to the last column of any row by typing [#ColumnNext|RowNumber]:
[$wsExample![#ColumnNext|4]3] 'the cell in row 3 that is above the first empty cell in row 4
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]
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 # Djeeni code enclosed in angular bracket
[#|rlRows] 'to specify outer Row Lists
or
[#|clColumns] 'to specify outer Column Lists
Example: Let's say you want to iterate through all the rows on your worksheet (with Djeeni name wsExample) using an outer Row List (with Djeeni name rlRows) and for each row through all the columns using an inner Column List (with Djeeni name clCells). To reference a current cell in for example a Cell Set process step, you would need to type in the following Djeeni formula:
wsExample![#|rlRows][#|clCells] 'Current cell reference to use in a Cell set
or
wsExample![#|rlRows]# 'we can leave out the |clCells part and []
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 data | [+#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] |
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.
#DjeeniFolder\Reports 'The subfolder Reports in the folder of Djeeni