Worksheet Hierarchies

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:

=SUM(ChildTable[Sales])

=SUM(ChildTable[Costs])

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.

Advertisements

0 Responses to “Worksheet Hierarchies”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




April 2010
M T W T F S S
« Mar   May »
 1234
567891011
12131415161718
19202122232425
2627282930  

%d bloggers like this: