Difference between revisions of "Web dependencies troubleshooting"
(Created page with "This page can help if you find that sheets do not update correctly when you change a parameter e.g. * Slicer * XL3Link * Unlocked cell == Symptom ==...") |
(→Solution) |
||
| Line 30: | Line 30: | ||
[[File:WebDepends3.png|thumb|none|Data page with helper formula]] | [[File:WebDepends3.png|thumb|none|Data page with helper formula]] | ||
| − | We can format this new cell so that the test if not visible, e.g. by setting the number format to | + | We can format this new cell so that the test if not visible, e.g. by setting the number format to <code>;;;</code>. |
Note that the formula references a cell in the grid, not somewhere outside the grid on that sheet. | Note that the formula references a cell in the grid, not somewhere outside the grid on that sheet. | ||
The same technique can be applies to [[Tables]], you could reference the table headers or the first row of data. | The same technique can be applies to [[Tables]], you could reference the table headers or the first row of data. | ||
Revision as of 15:59, 16 February 2015
This page can help if you find that sheets do not update correctly when you change a parameter e.g.
Symptom
- You have a worksheet which allows a user to set the report parameters.
- These drive grids or tables on another sheet.
- Formulae bring those numbers to the visible sheet.
- The numbers do not update when the selection is changed.
- But if you switch to the sheet with the grid on it then they do update.
XLCubed will attempt to work out the dependencies in your workbook, i.e. which grids need to be run for the visible sheet. This means not all grids must be run to display a sheet, improving performance.
If for some reason this can not be done then the grid may not refresh when required.
Solution
You can create a simple dependency to help out with this situation as seen in the below example.
We have a slicer connected to a Grid on Sheet2, and a formula displaying a number from that grid.
When this is published to the web the Indirect() formula means that the dependencies are not traced. We can add a formula to the front sheet to make sure the grid is refreshed when the main page is shown.
We can format this new cell so that the test if not visible, e.g. by setting the number format to ;;;.
Note that the formula references a cell in the grid, not somewhere outside the grid on that sheet.
The same technique can be applies to Tables, you could reference the table headers or the first row of data.
