Enabling Dashboard Mode for a Jet Reports worksheet will retain all Jet formulas when in Report Mode so that those formulas can be recalculated on the fly by changing filter values.
Using Dashboard Mode
To enable Dashboard Mode for a particular worksheet, add the +Dashboard tag to cell A1 of the worksheet. For example, cell A1 may have a value such as Auto+Hide+Values+Dashboard.
You will still need the +Values tag in cell A1 of at least one worksheet in the workbook in order for the report to be usable by Report Viewers .
When a sheet is in Dashboard Mode, the Jet functions will not be removed from the sheet in Report Mode, even though the workbook has the +Values tag. This allows users to change filter values on the Dashboard Mode sheet and have the Jet functions on the sheet update in real time as soon as they make changes, without having to re-run the report. Also, changes to worksheets in Dashboard Mode will not be overwritten when the report is put into Design Mode or refreshed.
Because evaluating Jet functions in report mode can take time, it is recommended that you keep the number of functions on a Dashboard Mode sheet fairly small. These functions will also be evaluated each time the workbook is opened even if the report is saved in Report Mode with the Values workbook tag present.
The NP(Slicer) Function
The NP(Slicer) function converts an Excel Slicer to a filter that can be used in Jet functions. In Report Mode, the NP(Slicer) function refreshes every time the Excel Slicer changes. The NP(Slicer) function can be embedded in other Jet functions or can be placed in its own cell allowing multiple functions to reference it as a filter.
The Excel Slicer referenced by the NP(Slicer) function must be associated with at least one pivot table in order for the NP(Slicer) function to update properly.