The control of the READ / WRITE activation of a cell is one of the core features to implement proper Excel Templates.
The most important
Named Ranges in this section (overview):
- The R/W control panel
- data1_CELLS01_ColumnDefinition
- data1_CELLS01_RowDefinition
- The R/W exception definition
- data1_CELLS01_ExceptionType0
- data1_CELLS01_ExceptionType1
- data1_CELLS01_ExceptionType2
- data1_CELLS01_ExceptionType3
- data1_CELLS01_ExceptionType4
1. The R/W control panel
As you learned here, the control panel consists of the two named ranges "data1_CELLS01_ColumnDefinition" and "data1_CELLS01_RowDefinition":
It directly controls how the cells within the "data1_CELLS01" range are treated in the READ and WRITE process.
On every READ and WRITE process, for every cell in the range "data1_CELLS01" the corresponding values in the R/W control panel is determined:
Basically, the lower of the two values determines, how the cell is treated:
- 1 = Do READ ONLY
The READ process (e.g. by double clicking an element in the dimension tree) will fill this cell with the value(s) from your application.
The WRITE process (e.g. by pressing the "Write" button) will ignore this cell.
Use cases: display of actuals which should not be written back (since this would involve the risk that actuals somehow could change) - 2 = Do READ + WRITE
The READ process (e.g. by double clicking an element in the dimension tree) will fill this cell with the value(s) from your application.
The WRITE process (e.g. by pressing the "Write" button) will writeback the value of this cell to your application.
Use cases: normal data entry cells - 3 = Do WRITE ONLY
The READ process will ignore this cell.
The WRITE process (e.g. by pressing the "Write" button) will writeback the value of this cell to your application.
Please note that the rule "the lowest value of the two" is valid here only in combination with a 0. If a 3 meets a 1, 2, or 4 then the cell type is 3.
Use cases: cells with formulas that calculate results and which should be written to the application e.g. for reporting purposes (mode 2 would delete the formula during the READ process) - 4 = Do CLEAR
The READ and the WRITE process will ignore this cell but the cell will be cleared.
Please note that the rule "the lowest value of the two" is valid here only in combination with a 0 and a 3. If a 4 meets a 1 or 2 then the cell type is 4.
Use cases: clear data entry cells on sum level since the aggregated values are shown in another row - 0 (or no value or any other value) = Do NOTHING
The cell is totally ignored.
Use cases: cells with normale Excel formulas e.g. for sum rows or sum columns; empty rows and columns in order to make the user interface more attractive; and many more
Examples from the Public Showcases:
- 0,1 and 2 in the Sales Forecast:
0 is used to "leave" the normal excel sum rows and blank rows - 2 and 4 in the Financial Forecast:
A formula is used to alternately activate / deactivate the (always hidden) sum level row and the (always visible) bottom level rows - 3 in the Financial Forecast:
3 is used to writeback calculations e.g. for deprecation to the applications (to make reporting easy since all results are in the applications)
2. The R/W exception type definitions
You can furthermore define so-called exceptions to the rules resulting from the R/W control panel.
Let`s take a look on the following case: this Template has rows with balances, whereas only the first value (= opening balance) must be entered and all the others are calculated. The row is therefore defined as type 3 since this is true for most of the values:
To solve the issue in this Template an exception can be defined:
Per sheet and Type there can be only 1 named range which has to be defined as a set of disconnected cells:
These are the currentyl supported named ranges for exception types:
- data1_CELLS01_ExceptionType0
- data1_CELLS01_ExceptionType1
- data1_CELLS01_ExceptionType2
- data1_CELLS01_ExceptionType3
- data1_CELLS01_ExceptionType4