As we’ve seen in the past, XL3Link is a flexible formula that is really worth knowing about to help easily build interlinked reports. To some extent it’s like a Swiss army knife – multi-purpose!  From highlighting a series on a chart to hosting a tooltip,  XL3Link is a flexible formula that can help make your reporting more interactive at multiple levels.

In this post we’ll be discussing a few more novel ways to use XL3Link.

Click to pick

There are a few ways of having a user select an item from a list to drive some other data in Excel and published to XLCubed Web Edition:

• XLCubed Slicers
• Using the Member Selector to select members in Grids and other XLCubed objects

And of course there are some ways that can also be driven from Excel ranges easily:

• XLCubed Slicers (again!)
• Excel Option buttons

But if you want a simpler looking option (or a slicker looking one!), it’s easy to drop in a few XL3Links:

Here’s a copy of a demo workbook. In it, we use the XL3Link formula (cells B2:B4) to populate cell D2 with a choice of members from the Region hierarchy. The Grid below then picks up the range and loads the data. To add a clarity we’ve added icons in cells A2:A4 to indicate which member is currently selected.

Report-based Layout and Filtering

We can build on the radio button idea above by controlling things other than text in cells. If we combine XL3Links with another useful set of XLCubed utility formulae — XL3SheetVisible, XL3RowVisible and XL3ColumnVisible — we can control the layout of our entire workbook with a click.

Let’s say that we wanted to build on the simple workbook that we created above, by adding a special detail worksheet for the Atlantic region. And perhaps our target audience aren’t interested in the ‘Furniture’ figures for Northwest Territories and Nunavut.

Here’s an updated copy of the demo workbook. We’ve added the Atlantic-only detail worksheet, as well as a few more pieces, controlled by the XL3Links in cells B2:B4. If you examine those XL3Link formulae, you’ll notice that they have 2 new pairs of parameters each:

• ‘Hide Furniture’: this parameter controls a set of XL3RowVisible formulae, hosted in a calculation column in the last column of the Grid, to control whether that row is shown to the user,
• ‘Atlantic Sheet’: this parameter controls the XL3SheetVisible formula in cell F3.

Switching Report Modes

There are many kinds of reports where it’s useful to switch a report between various modes. Sometimes an XLCubed Paged Viewport will be suitable, but here we’re going to combine what we learned above with the XL3SetProperty formula to manipulate an XLCubed Grid directly.

In this example workbook, instead of using the XL3Link formula, we’ve used the XLCubed Picture Link.

At the top of the workbook, we have 3 display modes that we’d like the user to be able to switch between just by clicking the ‘cycle’ button (the blue picture link on the right). Row 8 picks out the current display mode by using a simple VLookup formula. Cell A9 gives the picture link an easy source for the next display mode to cycle to, and cells C9:E9 do the real work – they contain XL3SetProperty formulae that, when the current mode (cell A8) is changed, update the Grid below to its new layout.

We’ve covered a few ways to extend the basic XL3Link formula by using it to activate other XLCubed features. So the XL3Link formula can be a very handy multi-tool to have in your toolbox. Its uses are only limited by your imagination!