Djeeni Documentation

User Tools

Site Tools

Trace:

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
en:user_guide:cellrange_basic [2020/06/16 22:56]
tiger [Accessing and setting data in cells or ranges] 		en:user_guide:cellrange_basic [2022/04/19 18:46] (current)
tiger [Accessing and Setting Data in Cells or Ranges]
Line 1: Line 1:
 {{indexmenu_n>5}} {{indexmenu_n>5}}
-====== Accessing and Setting Data in Cells or Ranges  ======+====== Working with Data in Cells/Ranges  ======
  
-In the [[en:user_guide:worksheet_basic|previous section]] we saw how to identify worksheets. The next step is to access the data on a worksheet.+In the [[en:user_guide:worksheet_basic|previous section]], we have learned how to identify worksheets. The next step is to access data on a worksheet.
  
 ===== Accessing cells and ranges ===== ===== Accessing cells and ranges =====
Line 9: Line 9:
  
 <code> <code>
-  [C:\folder\to\my\worksheet\myReport.xlsx]!C5+  [C:\folder\to\my\worksheet\myReport.xlsx]Sheet1!C5
 </code> </code>
  
-In Djeeni you have to first identified the worksheet by its [[en:concepts:Djeeniname|Djeeni name]] in the **WSheet Use** process step. After that reaching the cell is simply+In Djeeni you have to first identify the worksheet by its [[en:concepts:Djeeniname|Djeeni name]] in the **[[en:process_steps:worksheet:wsheet_use|WSheet Use]]** process step. After that reaching the cell is simply
  
 <code> <code>
Line 21: Line 21:
  
 <code> <code>
-  [C:\folder\to\my\worksheet\myReport.xlsx]!A2:G4  'MS Excel +  [C:\folder\to\my\worksheet\myReport.xlsx]Sheet1!A2:G4  'MS Excel 
-  wsMyReport!A2:G4                 -               'Djeeni+  wsMyReport!A2:G4                 -                     'Djeeni
 </code> </code>
  
 +==== Absolute cell reference ====
 +
 +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.
 ===== Setting the value of a cell ===== ===== 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:+To set the value of a cell the process step **[[en:process_steps:range_cell:cellset|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:
  
 <code> <code>
Line 67: Line 70:
  
 Example:  Example: 
-  * the name of an employee can be found in the cell wsReport!C2 .  +  * 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 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+  * the cell value of the found salary (denoted by the Djeeni name **ceEmployee**) must be written into **wsSalaries!D6**
  
 <code> <code>
-  Lookup Value     Djeeni name: ceEmployee +  Cell Lookup     Djeeni name: ceEmployee 
-                   Value: [=wsReport!C2] +                  Value: [=wsReport!C2] 
-                   Range: $wsEmployees!B1:E#RowEnd +                  Range: $wsEmployees!B1:E#RowEnd 
-   Cell Set        Cell: wsSalaries!D6 +  Cell Set        Cell: wsSalaries!D6 
-                   Value: $wsEmployees![+[$ceEmployee|column]+1][$ceEmployee|row]  +                  Value: $wsEmployees![+[#ceEmployee|column]+1][#ceEmployee|row]  
 </code> </code>
    
-You can use the found cell in many ways (using ceFound as the [[en:concepts:djeeniname|Djeeni name]]):+You can use the found cell in many ways (let's use **ceFound** as the [[en:concepts:djeeniname|Djeeni name]] of the found cell):
  
 <code> <code>
-[$ceFound|cell]       'refers to the found cell as Worksheet!ColumnRow+[#ceFound|cell]       'refers to the found cell as Worksheet!ColumnRow
                       'can be used at any cell reference providing                        'can be used at any cell reference providing 
                       'either the value or the location depending on the context                       'either the value or the location depending on the context
-[$ceFound|row]        'the row number of the found cell +[#ceFound|row]        'the row number of the found cell 
-[$ceFound|column]     'the column letter of the found cell+[#ceFound|column]     'the column letter of the found cell
 </code> </code>
  
Line 92: Line 95:
 ===== Setting a fixed value to a range ===== ===== 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:+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 **[[en:process_steps:range_cell:rangeset|Range Set]]** to achieve the same. Range Set works much like Cell Set:
  
 <code> <code>
-  Range Set    Cell: wsOutput!C3:D5+  Range Set    Range: wsOutput!C3:D5
                Value: 3             'number                Value: 3             'number
                              
-  Range Set    Cell: wsOutput!C3:D5+  Range Set    Range: wsOutput!C3:D5
                Value: Some Text     'text                Value: Some Text     'text
                              
-  Range Set    Cell: wsOutput!C3:D5+  Range Set    Range: wsOutput!C3:D5
                Value: 30-4-1969     'date                Value: 30-4-1969     'date
 </code> </code>
  
-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)+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**)
  
 <code> <code>
   Range Set     Range: wsYearlySell!D2:D[#RowEnd|C]   Range Set     Range: wsYearlySell!D2:D[#RowEnd|C]
-                Value: 2020+                Value: 2021
 </code> </code>
  
-Note how the range was specified using a [[en:concepts:djeeniformula|Djeeni formula]] to match the already entered days in Column C+Note how the range was specified using a [[en:concepts:djeeniformula|Djeeni formula]] to match the already entered days in Column **C**
  
 ===== Copy or Move a range ===== ===== 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 values must be copied/moved.+One of the most used data manipulation steps is to copy/move a range from one location to another. Djeeni has the **[[en:process_steps:range_cell:rangecopymove|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.
  

Page Tools

en/user_guide/cellrange_basic.1592340971.txt.gz · Last modified: 2020/06/16 22:56