You can use several optional parameters in your Workbook to control the behaviour in multi-sheet workbooks. Please remember that all named ranges in data1.io must be defined on a sheet (and not workbook) level.
The most important
The following optional parameters can be defined to control multi-sheet workbooks:
The following optional parameters can be defined to trigger certain actions:
How to use the multi-sheet parameters
Please be aware that a READ process is always executed when entering a sheet the first time after loading the workbook. This parameter rules the behaviour when re-entering a sheet after leaving it and then come back from another sheet.
0 = No refresh when re-entering the sheet
1 = Refresh (= READ) when re-entering the sheet
Simply add this new parameter to every page of your multi-sheet application and the sheet will be refreshed when you select a certain sheet of your workbook:
Use this parameter to (optinally) sync the READ process for several sheets in your Template file. Simply enter the number of the sheets that should be part of the READ process and seperate them with a comma and space. Please note that you must also add the number of the current sheet if you want to keep the "normal" read behaviour for this sheet. We recommend to use the Excel function SHEET() in order to set the definitions dynamically.
Look at this example: the Excel Template file has 3 sheets. The first two sheets must always be read (and written) together since sheet 1 contains the parameters for the calculations in sheet 2. Simply add the named range with the content "1, 2" in the main sheet ...
... as well as in the Parameter sheet:
For compatibility reasons the definition data1_CELLS01_SyncSheets_Read is also supported.
Use this parameter to (optionally) sync the WRITE process for several sheets in your Template file. Works like the data1_SyncSheets_Read.
Please note that there is an addtional mechanism for the <SYSTEM Delta> Parameter: if you use in all sheets the same <SYSTEM Delta> Parameter then the delete process will be executed only once before writing the first sheet back (and not before every sheet). Nevertheless we recommend to use different <SYSTEM Delta> Parameters in every sheet since this is more intuitive.
For compatibility reasons the definition data1_CELLS01_SyncSheets_Write is also supported.
1 = current sheet is visible
0 = current sheet is not visible
Can be used to setup a dynamic worksheet structure within the workbook depending on the currently logged in user ("object level security") and/or the currently selected element in the dimension tree.
How to use the trigger parameters
Any change in a cell in this name range will cause a read process. Can be used for dropdowns in your workbook to trigger a refresh.
Please note that this parameter was defined as data1_CELLS01_TriggerReadOnChange until November 2022.
With this named range you can select a dimension tree key in the dropdown, pass it to the tree and then trigger the read process (= refresh).
More exactly, you can define a variable number of named ranges starting with data1_Trigger_TreeSelectionAndReadOnChange, all of them will trigger the described process.
Please note that this parameter was defined as data1_CELLS01_TriggerTreeSelectionAndReadOnChange until November 2022.
With this named range you can define one or more cells of the sheet as trigger cells to execute a RECALCULATE of the formulas on the sheet and a refresh of the ROW FILTER ("auto filter"). The second feature is similar to the automatic "change trigger" for the visibility settings (see there).