Accessing and Setting Data in Cells or Ranges

In the previous section we saw how to identify worksheets. The next step is to access the 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

Setting the value of a cell

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.

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 found salary must be written into wsSalaries!D6
  Lookup Value     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 (using ceFound as the Djeeni name):

[#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    Cell: wsOutput!C3:D5
               Value: 3             'number
              
  Range Set    Cell: wsOutput!C3:D5
               Value: Some Text     'text
              
  Range Set    Cell: 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: 2020

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.