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.

Accessing cells and ranges

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

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. Setting a cell to the timestamp value A useful process step is Timestamp that sets a cell to the actual timestamp. Only the target cell must be specified. Accessing a cell by looking its value up 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

Setting a fixed value to a range

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

Copy or Move a range

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.