When viewing a high level summary report or dashboard, users often want to delve into more detail on a specific area. In some cases that may be a drill down, in many others it may be to a different view of the data or to an entirely different report. In the XLCubed example below, users can link from any one of the summary KPIs to a detail report showing product level detail for the selected KPI.
This is a fairly common requirement in reporting. In a standard Excel context, it would be easy to add a hyperlink formula to jump across to another sheet, but that’s just part of what’s needed. In this example we need to link in the context of the selected KPI, otherwise we would need a separate sheet for product detail on each KPI, far from ideal, especially in row-dynamic reports.
This type of limitation is one reason why you’ll often see workbooks with huge numbers of worksheets, which become unwieldy and horrible to maintain.
We need hyperlink functionality but also an ability to pass parameters (and of course a way for the pivot table to accept the incoming parameter…).
XLCubed makes it straightforward for non-technical users to build this type of contextual linking into reports through the XL3Link() formula. XL3Link has arguments which determine what is displayed in the cell, where it hyperlinks to, and what cell(s) parameters are passed from and to.
Unlike Pivot tables, XLCubed Grids and formulae can reference cell content as a filter, so the data on the ‘link to’ worksheet can update as soon as a new value is passed into the driving cell, retrieving the relevant data from whichever data source is involved.
The beauty of the approach is its simplicity. It’s something which most users can get to grips with quickly, and opens up huge flexibility in joined up reporting.
Last but not least, web and mobile deployment takes a matter of seconds. The report is published to XLCubed Web and from there browser and mobile app based users have access to the same report with the same chain of thought links. The links can be to different content in the same report, to a separate report, or a url to another application or website.
(This piece revisits content from our blog from several years back the missing link part 1 . The business requirement it addresses is now even more common, and still one not handled in native Excel.)