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/12/12 23:21] tiger [Looked up values] |
en:concepts:djeeniformula [2023/07/26 14:41] (current) tiger [Last row in a column] |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | {{indexmenu_n> | ||
====== Djeeni Formula ====== | ====== Djeeni Formula ====== | ||
Line 11: | Line 12: | ||
===== Djeeni formula extensions overview ===== | ===== 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 | [: | | 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 | [# | ||
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 # | ||
+ | |||
+ | < | ||
+ | # | ||
+ | </ |