Viewports

XLCubed Viewports help with the layout of variable length or width reports. For example they allow you to present a range from worksheet 1 on worksheet 2. This can be useful in many presentation scenarios, you can have separate worksheets for each business area, and bring them together on a formatted summary sheet.

They have a number of advantages over standard Excel camera objects (detailed here - http://blog.xlcubed.com/2012/07/a-snappy-fix-for-layout-problems-in-excel/). Firstly Viewports are scrollable. The Viewport can reference a much larger area on the source sheet, and if the Viewport control itself is smaller it will show vertical and horizontal scrollbars as required. This can be very useful for row dynamic reporting as the display area remains unchanged and additional parts of the report beneath the Viewport can remain in place even if the number of rows in the Viewport changes. They also support freeze panes so that the row and column headers can always be visible as you scroll.

Secondly they retain interactivity. If the Viewport contains a Grid, the grid is still drillable and editable even through the Viewport. If there are active cell links which update other parts of the workbook these are still interactive in the Viewport.

Lastly, they support named ranges being specified as the area (type in the name when picking a range). This works will when combined with specifying Named Ranges on Grids or Tables.

Viewports are added from the Visualise menu on the XLCubed Ribbon. You specify the source range to be displayed, and where to insert the viewport control. You can also define the placement options and border settings as shown below.

After insertion, the viewport control can be scrolled, any grids it contains can be drilled, and xl3link() and grid active cell settings are retained.

Excel Limitations

The viewport will only update when a cell value in the referenced range changes, so if it just contains a chart or image you may need to add a "Helper" cell to trigger the update. This can just be a reference to a cell that will be updated when the chart/image needs to be updated (it can be hidden or formatted so that it doesn't display anything)

Chart Viewports

In Version 9 it is possible to select a chart rather than a range to view. The main advantage of this is that the chart will be resized to the size if the viewport when it is drawn, so there aren't scaling issues that would happen if the image was stretched (e.g. text looks "fuzzy").

These are particularly of use on Dashboard sheets, where the viewport size often changes.

Another advantage is the chart will automatically refresh when the underlying data changes.

Web Limitations

Due to the way charts are resized:

• • You can not have two viewports on the same chart on one sheet.
    • But you can have them on different sheets.

• • Charts may overlap standard range based viewports.
    • In this case you can move the chart to a different sheet from the range viewport.