Unleash Data Using Djeeni Formulas

In the end, MS Excel data sit in the cells within worksheets. In this chapter, you can learn in detail how to

  1. access the data of a source cell;
  2. process it to be used as a result value in a target location (a cell on a target worksheet);
  3. create textual values that are used for other purposes like creating filenames or communicating;
  4. use Djeeni codes in Djeeni formulas.

Access data in cells

The basic access of a cell in Djeeni (just like in MS Excel) is specifying the worksheet name followed by the exclamation mark, column letter and row number:

  wsERPReport!F12

where the worksheet is identified by its Djeeni name instead of its physical filename and worksheet name in MS Excel. A range is also the same as in MS Excel: two cells on the same worksheet separated by colon.

  wsERPReport!B4:H5

But cells and ranges are seldom accessed by this basic method where all parts are fixed. The rest of this chapter explains the flexibility Djeeni provides in different situations to define the parts of a cell or range.

A worksheet in a list

Frequently, a data process involves many worksheets that should be consolidated or be created during the process. Djeeni uses workbook lists to go through the set of worksheets. A workbook list has a Djeeni name that can be used at every place where a worksheet should be specified. If the workbook list has the Djeeni name wlSubLedgers then

  wlSubLedgers!E35

is the cell E35 on the current worksheet in the workbook list.

Last row in a column

It is very common that the number of rows on a worksheet is not known upfront and is varying each time the process is used. The simple value #RowEnd can be used to identify the last row with data in a column.

  wsHeadcount!C#RowEnd

Itself, #RowEnd looks for the last row in column A. This behaviour can be modified by adding the column name after |:

  wsHeadcount!C#RowEnd|F

The above cell is the cell in column C with a row number equal to the last row in column F.

Last column in a row

Similar to #RowEnd, #ColumnEnd can be used to refer to the last column in a row:

  wsSupplies!#ColumnEnd9                  'last column in row 1
  wsSupplies![#ColumnEnd|5]9              'last column in row 5

Note that […] is used to separate the 5 (modifier for column end) from 9 (row number of the cell).

Process data

So, you can access any cell or range on any worksheet. Most of the time the data in the source cell is processed before it gets written into another target cell. Here are the processing options provided by Djeeni.

Excel functions

To process data in MS Excel itself, formulas can be used; and a dominant part of formulas use built-in MS Excel functions. For example, an MS Excel formula can sum the values of other cells / ranges:

  =SUM(A4:C5)

In Djeeni you can combine the MS Excel functions with Djeeni formulas:

  =SUM([:wsReport!A4:C5])

where [….] denotes the Djeeni formula part. If an MS Excel function has multiple parameters, all of them can get a value using Djeeni formulas:

  =IF([$wsMaster!B#]="C";[$wsMaster!C#];[$wsSource!A2])

Several different types of Djeeni formulas can be combined with MS Excel formulas:

  [$.....]        'an MS Excel cell reference
  [=.....]        'the actual value of the cell
  [:.....]        'a range
  [+.....]        'a calculation
  [#.....]        'special Djeeni codes

Calculations with data

Next to functions, MS Excel provides the opportunity to perform calculations in an MS Excel formula:

  =A5+B4

In Djeeni you can still use this format (suppose that cell A5 contains the number 2 and B4 contains 6):

  =[$wsInput!A5]+[$wsInput!B4]    'MS Excel calculates =A5+B4
  =[=wsInput!A5]+[=wsInput!B4]    'MS Excel calculates =2+6

or let Djeeni itself make the calculation using [+…]:

[+[=wsInput!A5]+[=wsInput!B4]]

NB: Any type of calculations will be denoted by [+…] but inside you can use + - * / as well.

Calculations using row number and column letter of a cell

At some cases, the row number and/or column letter of the cell are not known upfront:

  • it is calculated by #RowEnd or #ColumnEnd
  • result of a Cell Lookup process step
  • current row (#) in a row list

and it is needed to perform some calculations with these row numbers/column letters.

The most typical example is to find the first empty row below the last data row on a worksheet. #RowEnd gives the row number of last data row and 1 should be added. This calculated value should be used then in another operation. For example we need to write the word 'Total' below the last data row in column A on worksheet wsSummary:

  Cell Set    Cell: wsSummary!A[+#RowEnd+1]
              Value: Total

The row number and column letter of a cell found by Cell Lookup (with Djeeni name ceDay) are:

  [#ceDay|row]       'row number of found cell
  [#ceDay|column]    'column number of found cell

and a simple calculation let you access any cell related to this one. This feature of Djeeni is similar to the 'second half' of the VLOOKUP MS Excel function family but provides much more flexibility:

  wsA!C[+[#ceDay|row]-2]    'cell 2 rows above the previously found cell; in column C
                            'the previously found cell need NOT be in column C
                            'even further: it need NOT be on the same worksheet
  wsA![+[#ceDay|column]-1]1 'the column left to the found cell's column; in row 1

NB: for the duration of calculations Djeeni magically converts column letters into numbers and back.

The current row of a row list is denoted by #. It can be used just as flexible as the row number of a found cell by Cell Lookup: any combination with worksheets and columns are possible.

Create dynamic filenames and text values

Djeeni processes can

  • manipulate many worksheets with different filenames and worksheet names using workbook lists;
  • communicate with many users with different email addresses;
  • and set different cell values in row lists for each row.

The common problem is that the value (filename, email address etc.) is composed of several parts: partly literal values, partly dynamic values from cells. MS Excel provides the CONCATENATE function to compose such a value but in Djeeni it is much simpler. If the Djeeni formula [=….] is used to read the actual cell value of a cell then writing simply the parts after each other (without using an MS Excel formula that starts with = ) does the job.

Example: Suppose that we have some month names in column C on the worksheet wsMonths. The Djeeni process iterates through the rows of wsMonths and creates a new worksheet for each month in a workbook that is composed of the literal 'Report-' and the month name.

  WSheet Add  Excel name: Report-[=wsMonths!C#]

The Djeeni formula [=…..] can be used also in all parameters of an email (to, subject, body) to send personalized and with actual data filled emails.

With filenames it happens that any part of the physical location of a used worksheet (folder, filename and extension of the containing workbook; the MS Excel name of the worksheet) should be reused. In Djeeni this information can be accessed (let's say the Djeeni name of the worksheet is wsInput) using:

  [#wsInput|FOLDER]    'the folder part of the location of the containing workbook
  [#wsInput|FILE]      'the filename part of the location of the containing workbook
  [#wsInput|EXTENSION] 'the extension part of the location of the containing workbook
  [#wsInput|NAME]      'the MS Excel worksheet name of the worksheet

Djeeni codes in Djeeni formulas

Some information related to cells; filenames and worksheet names; row lists can be accessed using Djeeni codes. They were already discussed during examples in the user guide elsewhere. This section gives an overview of them in one place.

Djeeni codes start with # followed by a name and optionally a modifier after |. Next to constant Djeeni code names, Djeeni names can be also used as a Djeeni code.

Constant Djeeni codes

#RowEnd denotes the row number of the last non-empty cell in a column. By default, it looks in column A but can be modified to look in any column: #RowEnd|D

#ColumnEnd denotes the column letter of the last non-empty cell in a row. By default, it looks in row 1 but can be modified to look in any row: #RowEnd|12

# (empty name) refers to the current row number in a row list. In case there are multiple row lists embedded, # refers to the innermost row list. Can be modified by any row list name to refer to the current row number of that particular row list. Example: *|rlYears

Dynamic Djeeni codes

#wsDName (where wsDName is the Djeeni name of a used worksheet) with modifiers can be used to access different parts of the physical location of that worksheet:

  [#wsDName|FOLDER]    'the folder part of the location of the containing workbook
  [#wsDName|FILE]      'the filename part of the location of the containing workbook
  [#wsDName|EXTENSION] 'the extension part of the location of the containing workbook
  [#wsDName|NAME]      'the MS Excel worksheet name of the worksheet

#ceDName (where ceDName is the Djeeni name of a found cell by Cell Lookup) with modifiers can be used to access the row number, column letter and value of the cell:

  [#ceDName|CELL]    'the MS Excel reference to the cell
  [#ceDName|ROW]   'the row number of the found cell
  [#ceDName|COLUMN]  'the column letter of the found cell