Back from the Easter break now…
It occurred to me that a common requirement is for a workbook to reflect a hierarchical structure – for example:
- divisions/units within an organization
- geographical areas (country, region, city)
- product/part breakdown
- reporting period (year, quarter, month).
An obvious approach is to have a worksheet for each node in the hierarchy, with the leaf nodes holding the data, and the non-leaf nodes aggregating the data using formulas. (Note that I’m only considering single-dimension aggregation here, not hypercubes).
So I thought it would be useful to have a template worksheet that can be copied for each node in a hierarchy, and linked in to the hierarchy using the worksheet names. The aggregation formulas are already in place, and pick up the names of child nodes. And to aid navigation, we can have some hyperlinks to root, parent and child nodes.
My example is the start of a geographical hierarchy:
UK is the root; London and Brighton are in the South region. Each node worksheet has been copied from Template. Here’s the UK sheet:
The child nodes (i.e. the regions) are represented in a table, with the names entered in the Region column. The Sales and Costs columns pick up these names, to get the Sales/Costs values from the child worksheets. So, for example, cells E9 and F9 contain the formulas:
=INDIRECT($D9 & “!Sales”)
=INDIRECT($D9 & “!Costs”)
The UK node’s owns Sales and Costs values are just totals of the relevant columns:
The Sales/Costs cells are named ranges, needless to say.
A leaf node worksheet, such as London, has an empty child table, and literal values for Sales and Costs:
I’ve left the formulas in the table in case this node gets decomposed further, but it is in practice empty.
Here’s an intermediate node, for South region:
There are links to the root node (UK), the parent node (also UK here) and the child nodes. Each of these is a HYPERLINK formula that picks up the relevant node (i.e. worksheet ) name. For example, C2 contains the formula:
=HYPERLINK(“[" & Root & ".xlsm"]” & B2 & “!$A$1″, “link”)
Note that HYPERLINK needs a filename, even if the link is internal to this workbook. By convention, the workbook has the same name as the root node. So ‘Root’ is just a range name for B1 on the UK worksheet. The links all go to A1 on the target sheet. Similarly, G9 contains:
=HYPERLINK(“[" & Root & ".xlsm"]” & D9 & “!$A$1″, “link”)
So, to create a hierarchy node, you need to:
- copy the Template worksheet
- set Parent, Name, and Children (if any)
- set Sales and Costs values for leaf nodes.
No VBA so far, but I think it would be useful to build:
- a hyperlinked Table of Contents just to the leaf nodes – that is, where the data entry happens
- hierarchical summaries (i.e. each one on a single worksheet), for individual data categories (e.g. Sales).
More in due course.