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/calculate it to be used as a result value in a target location (i.e., 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.

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 that are separated by a 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.

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.

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.

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 #ColumnEnd) from 9 (row number of the cell).

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.

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]) 'Djeeni formula within an MS Excel formula

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])

And if you need to use an MS Excel formula inside of a process parameter value then you can use [+…]:

  WSheet Use  Filename: Report-[+TEXT(TODAY(),"yyyymmdd")].xlsx

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

Note: The difference between [$…] and [=…] is very important:

• [$….] is translated by Djeeni to MS Excel syntax and left to MS Excel to read the actual value: [$wsReport!A4] will be (let's say that wsReport is located at c:\myReport.xlsx, denoting worksheet Export) translated to '[c:\myreport.xlsx]Export'!A4. This reference can be further used in other MS Excel formulas.
• [=…] is executed by Djeeni reading the actual value of the cell: if [$wsReport!A4] contains the value 24 then the result of this formula is 24 that can be used for calculations or as text value. For date values [=…] reads the underlying number value.

For advanced tips about how to use Excel functions in different process steps see this page.

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 calculation will be denoted by [+…] but inside the formula, you can use all + - * / operators.

Working with dates in MS Excel is the most complex task causing the most headaches. For sure, most of us encountered this situation: a workbook is received with merged worksheets containing a date column. Surprisingly some dates are shown as numbers, while other dates are not recognized by MS Excel and cannot be used for further processing. Also, some dates are simply changing: it was 10 March 2022 at the sender, and it is just displayed as 3 October 2022 at the receiver (March = 3; October = 10).

The (almost) common denominator is the fact that all dates are internally stored in MS Excel as a number. Djeeni uses this number value whenever it is available for reading cell values and calculations. The consequence of it is that the target cells must be pre-formatted to short or long dates otherwise the number values will be seen instead of the date strings.

Dates are also discussed here.

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.

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 Use  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.

Another common situation is when the filename contains a date that is dynamic but not stored in any cell. We should use MS Excel formulas to calculate the values. Example: Let's suppose that we need to access a file every day that has a filename containing (among others) yesterday's date. The MS Excel functions TEXT and TODAY can be used inside the [+…] Djeeni formula to generate the date string dynamically every day:

  WSheet Use  Filename: Report-[+TEXT(TODAY()-1,"mmddyy")].xlsx

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

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.

#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

#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