This shows you the differences between two versions of the page.
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 | + | {{indexmenu_n> |
+ | ====== Djeeni | ||
- | One of the most powerful concepts of MS Excel is the formula. You can enter a fomula | + | One of the most powerful concepts of MS Excel is the formula. You can enter a formula |
< | < | ||
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 |
- | 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 | [: | | Named range | [:...] | 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) | [# | + | | Last column with data in row 1 | [# |
| Last column with data in a specific row | [# | | Last column with data in a specific row | [# | ||
- | | Last row with data (in column A) | [#RowEnd] or #RowEnd | - | [$wsName!C**# | + | | First column after last with data in row 1 | [# |
+ | | First column after last one with data in a specific row | [# | ||
+ | | Last row with data in column A | [#RowEnd] or #RowEnd | - | [$wsName!C**# | ||
| Last row with data in a specific column | [# | | Last row with data in a specific column | [# | ||
+ | | First row after the last one with data in column A | [#RowNext] or #RowNext | - | [$wsName!C**# | ||
+ | | First row after the last one with data in a specific column | [# | ||
| 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 | [# | | Current row/column of a specified Row/Column List | [# | ||
| Current worksheet in a workbook list | [[en: | | Current worksheet in a workbook list | [[en: | ||
| Workbook folder, filename and file extension of a worksheet | [# | | Workbook folder, filename and file extension of a worksheet | [# | ||
- | | Excel name of a worksheet | [# | + | | Excel name of a worksheet | [# |
| Calculations with values | [+calculation] | - | [+([# | | Calculations with values | [+calculation] | - | [+([# | ||
| Folder of the Djeeni client (relative folder) | [# | | Folder of the Djeeni client (relative folder) | [# | ||
- | ===== Refer to a cell or range ===== | + | ===== Referring |
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: | ||
</ | </ | ||
- | ===== Located | + | ===== Looked up values ===== |
- | After a value is located | + | After a value is looked up in a range using the **[[en: |
< | < | ||
Line 86: | Line 91: | ||
</ | </ | ||
- | refers to the cell of the found value. Example: if **Locate Value** finds the value in cell C4 on the worksheet wsSource then [# | + | refers to the cell of the found value. Example: if **Cell Lookup** finds the value in cell C4 on the worksheet wsSource then [# |
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: | ||
</ | </ | ||
- | Example: if **Locate Value** finds the value in cell C4 on the worksheet wsSource then [# | + | Example: if **Cell Lookup** finds the value in cell C4 on the worksheet wsSource then [# |
===== Last row in a column ===== | ===== Last row in a column ===== | ||
Line 109: | Line 114: | ||
< | < | ||
[$wsExample!C[# | [$wsExample!C[# | ||
+ | </ | ||
+ | |||
+ | ===== Next row in a column ===== | ||
+ | |||
+ | You can find the next row after the last one with data in column A by typing **[# | ||
+ | |||
+ | < | ||
+ | [$wsExample!C# | ||
+ | </ | ||
+ | |||
+ | You can refer to the first row after the last one with data of any column by typing **[# | ||
+ | |||
+ | < | ||
+ | [$wsExample!C[# | ||
</ | </ | ||
Line 123: | Line 142: | ||
< | < | ||
[$wsExample![# | [$wsExample![# | ||
+ | </ | ||
+ | |||
+ | ===== Next column in a row ===== | ||
+ | |||
+ | You can find the first column after the last one with data in row 1 by typing **[# | ||
+ | |||
+ | < | ||
+ | [$wsExample!# | ||
+ | </ | ||
+ | |||
+ | You can refer to the last column of any row by typing **[# | ||
+ | |||
+ | < | ||
+ | [$wsExample![# | ||
</ | </ | ||
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 | + | 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 |
< | < | ||
- | [#|rlRowListDjeeniName] 'to specify outer Row Lists | + | [#|rlRows] 'to specify outer Row Lists |
</ | </ | ||
Line 151: | Line 184: | ||
< | < | ||
- | [#|clColumnListDjeeniName] 'to specify outer Column Lists | + | [#|clColumns] 'to specify outer Column Lists |
</ | </ | ||
- | Business | + | Example: Let's say you want to iterate through all the rows on your worksheet (with Djeeni name wsExample) using an **outer Row List** |
< | < | ||
- | wsExample![# | + | wsExample![# |
</ | </ | ||
Line 163: | Line 196: | ||
< | < | ||
- | wsExample![# | + | wsExample![# |
</ | </ | ||
===== 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 | [+# | + | | The row below the last row with data | [+# |
| The column right to the last column with data | [+# | | The column right to the last column with data | [+# | ||
| 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 # | To use a relative location you can use the # | ||
+ | |||
+ | < | ||
+ | # | ||
+ | </ |