User Tools

Site Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
en:concepts:djeeniformula [2020/05/16 23:33]
tiger [Current row in Row/Column Lists]
en:concepts:djeeniformula [2023/07/26 14:41] (current)
tiger [Last row in a column]
Line 1: Line 1:
-====== Djeeni Formulas ======+{{indexmenu_n>2}} 
 +====== Djeeni Formula ======
  
-One of the most powerful concepts of MS Excel is the formula. You can enter a fomula like this:+One of the most powerful concepts of MS Excel is the formula. You can enter a formula like this:
  
 <code> <code>
Line 9: Line 10:
 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. 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 =====+===== 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.+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 |||| ^ Djeeni formula extensions overview ||||
Line 19: Line 20:
 | Cell Value   | [=...] | value of the cell D4 | [=wsName!D4] | | Cell Value   | [=...] | value of the cell D4 | [=wsName!D4] |
 | Named range | [:...] | namedRange | [:namedRange] | | Named range | [:...] | namedRange | [:namedRange] |
-| Cell reference of a located value | [# ... %%|%%cell]| - | [#ceValue%%|%%cell] | +| Cell reference of a found cell | [# ... %%|%%cell]| - | [#ceCell%%|%%cell] | 
-| Row number of a located value | [# ... %%|%%row]| - | [#ceValue%%|%%row] | +| Row number of a found cell | [# ... %%|%%row]| - | [#ceCell%%|%%row] | 
-| Column letter of a located value | [# ... %%|%%column]| - | [#ceValue%%|%%column] | +| Column letter of a found cell | [# ... %%|%%column]| - | [#ceCell%%|%%column] | 
-| Last column with data (in the first row| [#ColumnEnd] or #ColumnEnd | - | [$wsName!**[#ColumnEnd]**2] |+| Last column with data in row | [#ColumnEnd] or #ColumnEnd | - | [$wsName!**[#ColumnEnd]**2] |
 | Last column with data in a specific row | [#ColumnEnd%%|%%Row] | - | [$wsName!**[#ColumnEnd%%|%%2]**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**] |+| 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**] | | 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 the innermost Row/Column List | [#] or # | - | [$wsName!F**#**] |
 | Current row/column of a specified Row/Column List | [#%%|%%DjeeniName]  | - | [$wsName!F**[#%%|%%rlRowList]**] | | Current row/column of a specified Row/Column List | [#%%|%%DjeeniName]  | - | [$wsName!F**[#%%|%%rlRowList]**] |
 | Current worksheet in a workbook list | [[en:concepts:djeeniname|Djeeni name]] of the list | - | [$**wlName**!B5] | | Current worksheet in a workbook list | [[en:concepts:djeeniname|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] | | 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] |+| Excel name of a worksheet | [#worksheet%%|%%Name] | - | [#wsName%%|%%Name] |
 | Calculations with values | [+calculation] | - | [+([#RowEnd]-1) * ([#ColumnEnd]-2)] | | Calculations with values | [+calculation] | - | [+([#RowEnd]-1) * ([#ColumnEnd]-2)] |
 | Folder of the Djeeni client (relative folder) | [#DjeeniFolder] or #DjeeniFolder | - | #DjeeniFolder\finance\reports | | Folder of the Djeeni client (relative folder) | [#DjeeniFolder] or #DjeeniFolder | - | #DjeeniFolder\finance\reports |
  
-===== Refer to a cell or range =====+===== Referring 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: In an Excel formula, you can refer to a cell on the current worksheet by giving its column letter and row number:
Line 78: Line 83:
 </code> </code>
  
-===== Located values =====+===== Looked up values =====
  
-After a value is located in a range using the **[[en:process_steps:range_cell:locatevalue|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+After a value is looked up in a range using the **[[en:process_steps:range_cell:locatevalue|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
  
 <code> <code>
Line 86: Line 91:
 </code> </code>
  
-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] .+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: It is also possible to use the row number and the column letter of the cell where the value is found:
Line 95: Line 100:
 </code> </code>
  
-Example: if **Locate Value** finds the value in cell C4 on the worksheet wsSource then [#ceValue|row] is 4 while [#ceValue|column] is C.+Example: if **Cell Lookup** 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 ===== ===== Last row in a column =====
Line 109: Line 114:
 <code> <code>
   [$wsExample!C[#RowEnd|B]]    'the cell in column C that is next to the last cell in column B   [$wsExample!C[#RowEnd|B]]    'the cell in column C that is next to the last cell in column B
 +</code>
 +
 +===== Next row in a column =====
 +
 +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**:
 +
 +<code>
 +  [$wsExample!C#RowNext]    'the cell in Column C that is in the row of the first empty cell in column A
 +</code>
 +
 +You can refer to the first row after the last one with data of any column by typing **[#RowNext%%|%%ColumnLetter]**:
 +
 +<code>
 +  [$wsExample!C[#RowNext|B]]    'the cell in column C that is next to the first empty cell in column B
 </code> </code>
  
Line 123: Line 142:
 <code> <code>
   [$wsExample![#ColumnEnd|4]3]    'the cell in row 3 that is above the last cell in row 4   [$wsExample![#ColumnEnd|4]3]    'the cell in row 3 that is above the last cell in row 4
 +</code>
 +
 +===== Next column in a row =====
 +
 +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**:
 +
 +<code>
 +  [$wsExample!#ColumnNext3]    'the cell in row 3 that is under the first empty cell in row 1
 +</code>
 +
 +You can refer to the last column of any row by typing **[#ColumnNext%%|%%RowNumber]**:
 +
 +<code>
 +  [$wsExample![#ColumnNext|4]3]    'the cell in row 3 that is above the first empty cell in row 4
 </code> </code>
  
Line 142: Line 175:
 ==== Nesting ==== ==== 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+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
  
 <code> <code>
-[#|rlRowListDjeeniName]    'to specify outer Row Lists +[#|rlRows]    'to specify outer Row Lists 
 </code> </code>
  
Line 151: Line 184:
  
 <code> <code>
-[#|clColumnListDjeeniName]    'to specify outer Column Lists+[#|clColumns]    'to specify outer Column Lists
 </code> </code>
  
-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:+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:
  
 <code> <code>
-wsExample![#|rlRowListDjeeniName][#|clColumnListDjeeniName]   'Current cell reference to use in the Cell parameter of a Cell set+wsExample![#|rlRows][#|clCells]   'Current cell reference to use in a Cell set
 </code> </code>
  
Line 163: Line 196:
  
 <code> <code>
-wsExample![#|rlRowListDjeeniName]#    'we can leave out the |clColumnListDjeeniName part and square brackets+wsExample![#|rlRows]#    'we can leave out the |clCells part and []
 </code> </code>
 ===== Calculations ===== ===== Calculations =====
Line 171: Line 204:
 ^ Example calculations || ^ Example calculations ||
 ^ Expected result ^ Djeeni formula ^ ^ Expected result ^ Djeeni formula ^
-| The row below the last row with value | [+#RowEnd+1] |+| The row below the last row with data | [+#RowEnd+1] |
 | The column right to the last column with data | [+#ColumnEnd+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 three target rows for every source row in a row list | [+#*3], [+#*3+1], [+#*3+2] |
Line 179: Line 212:
  
 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. 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.
 +
 +<code>
 +  #DjeeniFolder\Reports 'The subfolder Reports in the folder of Djeeni
 +</code>