Web dependencies troubleshooting

This page can help if you find that sheets do not update correctly when you change a parameter e.g.

Symptoms

  • 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 need to be run to display a sheet, improving performance.

If for some reason this cannot be done then the grid may not refresh when required.

Problem

We have a slicer connected to a Grid on Sheet2, and a formula displaying a number from that grid.

Main page with Slicer
Data page with Grid

When this is published to the web the Indirect() formula means that the dependencies are not traced.

Solution

You can create a simple dependency to help out with this situation as seen in the below example.

We can add a formula to the front sheet to make sure the grid is refreshed when the main page is shown.

Data page with helper formula

We can format this new cell so that the test is 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.

Manual Configuration

As of Version 9.1 you can configure the dependencies yourself.