Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
en:user_guide:worksheet_lists [2020/06/12 14:28] tiger [Referring to the current worksheet and row in a workbook or row list] |
en:user_guide:worksheet_lists [2021/01/10 14:44] (current) tiger [Know your process for one worksheet; then switch to multiple] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{indexmenu_n> | {{indexmenu_n> | ||
| - | ====== Working | + | ====== Working |
| Naming and using a worksheet is a very useful concept in case of a few, known worksheets. There are cases, though, when several or many worksheets with the same structure must be batch processed or created. Typical cases are consolidation or splitting tasks. | Naming and using a worksheet is a very useful concept in case of a few, known worksheets. There are cases, though, when several or many worksheets with the same structure must be batch processed or created. Typical cases are consolidation or splitting tasks. | ||
| Line 7: | Line 7: | ||
| ===== Consolidating data from multiple source worksheets to a single target worksheet ===== | ===== Consolidating data from multiple source worksheets to a single target worksheet ===== | ||
| - | The source worksheets that should be consolidated do already exist having their filenames at hand. Therefore best practice is to use the **WBook List Start** and **WBook List Next** process steps (under categories Worksheet and If / Lists on the [[en: | + | The source worksheets that should be consolidated do already exist having their filenames at hand. Therefore best practice is to use the **[[en: |
| Under the workbook list parameter the workbooks of the source worksheets can be specified using: | Under the workbook list parameter the workbooks of the source worksheets can be specified using: | ||
| * wildcard characters ? and * in filenames (e.g. c: | * wildcard characters ? and * in filenames (e.g. c: | ||
| * multiple folders or files separated by ; (e.g. c: | * multiple folders or files separated by ; (e.g. c: | ||
| - | |||
| - | |||
| ==== Know your process for one worksheet; then switch to multiple ==== | ==== Know your process for one worksheet; then switch to multiple ==== | ||
| - | The first step is to specify the process for one source worksheet just like it is done for a single worksheet case. Use **WSheet Use** to specify the only source | + | The first step is to specify the process for one source worksheet just like it is done for a single worksheet case. Use **[[en: |
| - | - replace **WSheet Use** for the only source worksheet by **WBook List Start** for all the worksheets at the beginning (NB: keep **WSheet Use** for the target!); | + | - replace **WSheet Use** for the only source worksheet by **WBook List Start** for all the worksheets at the beginning |
| - specify all the worksheets in the Workbook list parameter | - specify all the worksheets in the Workbook list parameter | ||
| - add **WBook List Next** to the end of the operations; | - add **WBook List Next** to the end of the operations; | ||
| - | - use the [[en: | + | - use the [[en: |
| - replace the target cell of cell and range operations to allow for | - replace the target cell of cell and range operations to allow for | ||
| * appending rows; | * appending rows; | ||
| * inserting rows; and | * inserting rows; and | ||
| - | * accumulating values instead of overwriting the same cell again and again. See detailed at the below sections. | + | * accumulating values instead of overwriting the same cell again and again. See these steps detailed at the sections |
| Done! | Done! | ||
| Line 83: | Line 81: | ||
| Cell Set Cell: wsTarget!C5 | Cell Set Cell: wsTarget!C5 | ||
| Value: 1 | Value: 1 | ||
| - | Cell Set Cell: wsTarget!C4 | + | Cell Set Cell: wsTarget!C4 |
| Value: [=wsSource!D5] | Value: [=wsSource!D5] | ||
| Line 103: | Line 101: | ||
| - | ===== Dividing | + | ===== Split data from a source worksheet to multiple target worksheets ===== |
| A typical task is to split an MS Excel source worksheet from an external source (it can be e.g. a supplier provided data set or an IT system report) according to certain criteria. The question is, how it is decided which target worksheets should be created? They do not exist before the process so the filenames are not known. Further, the set of the to be created files can be different every time based on source or supplementary info. Djeeni can create target worksheets in three ways: | A typical task is to split an MS Excel source worksheet from an external source (it can be e.g. a supplier provided data set or an IT system report) according to certain criteria. The question is, how it is decided which target worksheets should be created? They do not exist before the process so the filenames are not known. Further, the set of the to be created files can be different every time based on source or supplementary info. Djeeni can create target worksheets in three ways: | ||
| Line 110: | Line 108: | ||
| - If the target worksheets are to be created using the information from the source worksheet itself then the **WSheet Split** process step can be used | - If the target worksheets are to be created using the information from the source worksheet itself then the **WSheet Split** process step can be used | ||
| + | Let's see these ways in details. | ||
| ==== Creating target worksheets in a WBook List ==== | ==== Creating target worksheets in a WBook List ==== | ||
| Line 193: | Line 192: | ||
| </ | </ | ||
| ==== Selecting ranges in source by lookup ==== | ==== Selecting ranges in source by lookup ==== | ||
| - | ===== Referring to the current worksheet and row in a workbook or row list ===== | ||
| - | Workbook lists and row lists have their own [[en: | + | Certain source data sets contain ranges that are identified by a start value and an end value. For example, a range of one month that starts on the first of the month and lasts till the first of the next month. In this case, the range can be identified by the two cells that can be found using **Cell Lookup**. The steps are: |
| + | - Find the first cell of the range using **Cell Lookup** | ||
| + | - Find the last cell (or the cell after the last cell) of the range using **Cell Lookup** | ||
| + | - Specify the range using **Range Use** and the two cells | ||
| + | |||
| + | < | ||
| + | WSheet Use Djeeni name: wsData | ||
| + | Cell Lookup | ||
| + | Value: 1-11-2020 | ||
| + | Range: wsData!C1: | ||
| + | Cell Lookup | ||
| + | Value: 1-12-2020 | ||
| + | Range wsData!C1: | ||
| + | Range Use | ||
| + | Range: wsData!C[# | ||
| + | </ | ||
| + | ===== Referring to the current worksheet in a workbook list ===== | ||
| + | |||
| + | Workbook | ||
| < | < | ||
| WSheet Use | WSheet Use | ||
| Cell Set Cell: wsTarget!C3 | Cell Set Cell: wsTarget!C3 | ||
| - | |||
| - | Row List Start | ||
| - | Cell Set Cell: rlMaster!C3 | ||
| WBook List Start Djeeni name: wlTarget | WBook List Start Djeeni name: wlTarget | ||
| Cell Set Cell: wlTarget!C3 | Cell Set Cell: wlTarget!C3 | ||
| </ | </ | ||
| + | |||
| + | ===== Referring to the current row in a row list ===== | ||
| At row lists, there is always a current row that is referred by # instead of an actual row number. In case of nested row lists, # can be extended by the Djeeni name of the Row List. | At row lists, there is always a current row that is referred by # instead of an actual row number. In case of nested row lists, # can be extended by the Djeeni name of the Row List. | ||
| < | < | ||
| - | Row List Start Djeeni name: rlOuter | + | WSheet Use |
| - | Row List Start Djeeni name: rlInner | + | WSheet Use |
| - | Cell Set Cell: rlOuter!D[# | + | Row List Start Djeeni name: rlOuter |
| - | Cell Set Cell: rlInner!D# ' | + | |
| + | Row List Start Djeeni name: rlInner | ||
| + | | ||
| + | Cell Set | ||
| + | Cell Set | ||
| </ | </ | ||