Working with Data in Cells/Ranges

In the previous section, we have learned how to identify worksheets. The next step is to access data on a worksheet.

In MS Excel a cell (let it be C5) on a worksheet is accessed by specifying the containing workbook and worksheet followed by the column letter and row number:

  [C:\folder\to\my\worksheet\myReport.xlsx]Sheet1!C5

In Djeeni you have to first identify the worksheet by its Djeeni name in the WSheet Use process step. After that reaching the cell is simply

  wsMyReport!C5

where wsMyReport is the Djeeni name of the worksheet. Ranges (for example from A2 to G4) also follow the MS Excel convention by separating the top-left and bottom-right cells of the range with a colon:

  [C:\folder\to\my\worksheet\myReport.xlsx]Sheet1!A2:G4  'MS Excel
  wsMyReport!A2:G4                 -                     'Djeeni

MS Excel uses the $ notation to prevent formulas to change their cell references when copied or moved from one cell to another (e.g. $F7,T$23). In Djeeni, cells are referenced using the above Djeeni notation that does not require and also does not allow $. Never use $ in any cell reference within Djeeni.

To set the value of a cell the process step Cell Set (under category Range / Cell) can be used. The value can be a formula letting Djeeni calculate the final value of the cell. The simplest example is set a cell (take C3 on the worksheet identified as wsOutput) to a literal value:

  Cell Set    Cell: wsOutput!C3
              Value: 3             'number
              
  Cell Set    Cell: wsOutput!C3
              Value: Some Text     'text
              
  Cell Set    Cell: wsOutput!C3
              Value: 30-4-1969     'date

Let's take another example: there is a source data worksheet (identified as wsInput) and the task is to set cell C5 on the target data worksheet (identified as wsOutput) to the value of B4 of wsInput:

  Cell Set    Cell: wsOutput!C5
              Value: [$wsInput!B4]

Note that you need not to use the = sign to get the value of cell B4 on wsInput. But the cell is referenced through Djeeni so [$ … ] should be used to denote that. This is called a Djeeni formula.

A useful process step is Timestamp that sets a cell to the actual timestamp. Only the target cell must be specified.

When it is about data manipulation, an MS Excel user gets sooner than later to the famous VLOOKUP function family that performs the combination of two operations:

• first find a cell by value;
• then immediately return the value of another cell related to the found cell

and is limited in:

• where to look for the cell (only in a column or row)
• what relation can be between the found cell and the returned cell (same row different column or vice versa);
• forgetting the found cell immediately (you cannot relate two or more cells to the found cell)

Djeeni makes looking up cells and then working with them simpler yet much more powerful than MS Excel. The process step Cell Lookup performs the first part of VLOOKUP and finds a cell that contains the specified value. When the cell is found it will be remembered by naming it. Using this Djeeni name of the cell at a later step allows you to relate any other cell /range to the found one and perform whatever operation you want.

Example:

• the name of an employee can be found in the cell wsReport!C2 .
• the corresponding salary of this employee is on the wsEmployees master data worksheet. The wsEmployees worksheet contains employee names in column B and E; the corresponding salaries are in column C and F.
• the cell value of the found salary (denoted by the Djeeni name ceEmployee) must be written into wsSalaries!D6

  Cell Lookup     Djeeni name: ceEmployee
                  Value: [=wsReport!C2]
                  Range: $wsEmployees!B1:E#RowEnd
  Cell Set        Cell: wsSalaries!D6
                  Value: $wsEmployees![+[#ceEmployee|column]+1][#ceEmployee|row]  

You can use the found cell in many ways (let's use ceFound as the Djeeni name of the found cell):

[#ceFound|cell]       'refers to the found cell as Worksheet!ColumnRow
                      'can be used at any cell reference providing 
                      'either the value or the location depending on the context
[#ceFound|row]        'the row number of the found cell
[#ceFound|column]     'the column letter of the found cell

How would you insert the same value to a whole range? In MS Excel you can select the range, enter the value in one of the cells and press Ctrl+Enter. In Djeeni you can use the process step Range Set to achieve the same. Range Set works much like Cell Set:

  Range Set    Range: wsOutput!C3:D5
               Value: 3             'number
              
  Range Set    Range: wsOutput!C3:D5
               Value: Some Text     'text
              
  Range Set    Range: wsOutput!C3:D5
               Value: 30-4-1969     'date

Example: The input data source (wsYearlySell) contains dates from a certain year in months (column B) and day (column C) format. The data must be added to a multi-year summary sheet. Before adding the dates they must be completed by the year part (in Column D)

  Range Set     Range: wsYearlySell!D2:D[#RowEnd|C]
                Value: 2021

Note how the range was specified using a Djeeni formula to match the already entered days in Column C

One of the most used data manipulation steps is to copy/move a range from one location to another. Djeeni has the Copy/Move Range to process step to carry out this operation, including the options that are provided by MS Excel itself. It can be specified if the range is coped or moved; if it overwrites the target range or will be inserted; and if only values or also formatting and formulas must be copied/moved.