Smart Inter-Workbook Links

In a previous post, I looked at how to support ‘rolling-over’ data from one period to the next. In this case, it was done in a forward direction: from Report N to Report N+1. As this involved creating a new workbook from a template, the non-data content of the workbooks was identical.

Alternatively, you might want backward links from Report N to Report N-1. You could just have inter-workbook formulas, like this:

=Report_2009_05_01.xls!Prev_Data

with the workbook name, and posibly a full path. However, in next month’s report (i.e. July), this will need to be:

=Report_2009_06_01.xls!Prev_Data

or whatever is required by the frequency of the reports.

So I was wondering how you could ‘soft-code’ the links, so that the previous workbook name was generated from:

  • a standard prefix, such as “Report_”
  • the current report’s date
  • a reporting frequency (e.g. ‘m’ for monthly, as in the DateAdd function)
  • a number of periods to go back (e.g. -3 for quarterly).

Having identified and opened the previous workbook, we then look for named ranges in the current workbook whose names have a standard prefix, say “Prev_”. The values of these ranges (single or multiple cells) are then set to be the values of the corresponding ranges in the previous workbook (with a “Curr_” prefix, say):

Report N-1: Curr_Xxx   –> Report N: Prev_Xxx          (for all Xxx)

The whole update process is triggered by Workbook_Open in Report N (with a YesNo MsgBox to make it optional).

Note that the named ranges only ever contain data, not formulas. The linking is entirely ‘soft’, using just the range naming convention.

As usual, there’s an assumption that the workbooks are in the same folder, but we could handle a multi-folder structure, providing that it’s date-based (e.g. year folders).

The difference from the earlier approach is that there are no constraints on the structure of the workbooks. They can be the same or different; some or all of the periodic data can be rolled-over. All that is required is that:

  • the workbooks follow the naming convention
    (in this case <wbprefix>_yyyy_mm_dd.xls)
  • Ranges with corresponding names in the two workbooks have the same size and shape.

If you’re interested, here’s the ThisWorkbook module. When you import it, it comes in as a Class module called ‘ThisWorkbook’; you need to copy and paste the code into the proper ThisWorkbook module. The prefixes and other settings are constants in the module header.

Advertisements

0 Responses to “Smart Inter-Workbook Links”



  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




June 2009
M T W T F S S
« May   Jul »
1234567
891011121314
15161718192021
22232425262728
2930  

%d bloggers like this: