Excel Magic Consolidator(MagicCons.xls)
by David Hager
Copyright @1999 All Rights Reserved

Feel free to use this technique in your Excel projects, as long as you
include a statement as to the original source.

There are no examples of the formulas referred to in this text in the
working xl file, but you should be able to construct your own, based 
on the following information.

1) Basic Instructions

   a) What does it do?

It allows the user to write formulas on the consolidation worksheet (called 
"Summary" by default) that act on the same cell from every worksheet in the 
workbook. The results of the formulas change dynamically as sheets are 
added/deleted from the workbook. Also, the summary sheet can be located at
any position within the workbook. There is no VBA or xlm macro code used
in this solution. All of the work is done by defined name formulas.

   b) Writing the formulas

As an example, if you type the formula =SUM(cCell) in cell B4 on the Summary
worksheet, that formula will return the sum of cell B4 for every worksheet
in the workbook, since cCell as used in cell B4 returns the array of entries
for those worksheets. Information about using arrays that return entries 
from cells offset from the cells they are used in can be found in 2a.

   c) Changing the consolidation sheet name

To change the consolidation sheet name, go to Insert, Name, Define in the 
menu. The named formula called TheSummarySheetName is defined as ="Summary". 
This means that the worksheet named "Summary" is the only sheet in the 
workbook that can be used with the consolidation formulas. If, for example, 
you want change the name to "ConsSheet", then you need to define 
TheSummarySheetName as ="ConsSheet". Of course, you must have a worksheet 
by that name as well.

   d) Exporting to an existing workbook

To export this functionality to another workbook, you need to use the Move 
or Copy menu item from the popup menu that is available when you right-click 
a worksheet tab. In this case, right-click the Summary tab (or whatever 
name you may have changed it to). Then,  select the desired workbook and 
sheet location from the dialog box and the checkbox named "Create a copy"
and press Enter. All of the defined name formulas will copy over to the 
new workbook (and of course it is not necessary for your workbook to be
named MagicCons.xls). Note that a new workbook must first be saved for this
technique to work.


2) How does it work?

   a) Understanding the formulas

All of the formulas used to create the consolidation are defined name
formulas. You can view them by selecting Insert, Name, Define from the 
menu. Do not change these formulas unless you understand how they work.

There are 4 constants defined for use in the z-relative formulas. By default,
the defined name formulas down, left, right and up have been assigned a 
value of 1.

TheSummarySheetName is defined as:
="Summary"

This is a defined name formula that sets the name of the worksheet to be
used as the consolidation worksheet. 

ThisSheet is defined as:
=LEFT(GET.DOCUMENT(1),FIND("]",GET.DOCUMENT(1)))&TheSummarySheetName

This formula returns the sheet name of the consolidation worksheet in the 
form "[MagicCons.xls]Summary". This string will be different if used in
another workbook and/or with a different consolidation worksheet. This 
string will be used to match the same string in the TheSheets formula.

TheSheets is defined as:
=IF(GET.WORKBOOK(1)=ThisSheet,"","'"&GET.WORKBOOK(1)&"'")

The GET.WORKBOOK(1) xlm macro function returns an array of names for the 
worksheets in the workbook. This formula modifies that array to return an 
array with an empty string for the array item corresponding to the 
consolidation worksheet. NOTE: You can modify this formula to exclude 
worksheets other than the "Summary" sheet (if you know how <g>).

cCell is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()))))

The concatenated string in the formula INDIRECT(TheSheets&"!"&ADDRESS(ROW() 
,COLUMN())) creates an array of cell addresses for the cell in which the 
formula resides all of the worksheets in the workbook. The worksheet cell 
address for the position on the consolidation worksheet is constructed 
incorrectly by design so that a circular reference to that cell will not 
be created. When that string is acted on by the INDIRECT function, a 3-D or
z-range is created. Due to a glitch in how Excel returns this array, it 
must be acted on by the N function to produce a true array.

cCellDown is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()+down,COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()+down,COLUMN()))))

cCellLeft is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()-left)))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()-left))))

cCellRight is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()+right)))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()+right))))

cCellUp is defined as:	
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-up,COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-up,COLUMN()))))

Realize that in order to use offset arrays of differing dimensions, you will
have to define you own hard-coded formulas, such as:

cCellUp4 is defined as:	
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-4,COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-4,COLUMN()))))


   b) Using arrays with "non-3D enabled" Excel functions

There are quite a few Excel functions that do not work with the 3D ranges
that are inherent to Excel. For example, the MATCH function cannot be as
shown in the following formula.

=MATCH(2, Sheet1:Sheet7!C1, 0) 

However, this formula does work as expected.

=MATCH(2, cCell, 0)

In the former case, the 3D range reference Sheet1:Sheet7!C1 does not give
an array that the MATCH function can operate on. The latter case contains
the readable array cCell (which can be viewed by evaluating that portion 
of the formula in the formula bar) that MATCH does work with.

   c) Z-relative array formulas 

Since real arrays are returned by cCell and its cousins, they can be used
just like any normal range is used in an array formula.

3) Problems

   a) Circular references

If you try to use the consolidation formulas on any other worksheet than
the designated consolidation sheet, a circular reference will be created.
Do not use these formulas on other worksheets!

   b) Sheets other than worksheets

The presence of charts and Excel5 dialog sheets do not interfere with the
workings of the consolidation formulas. However, an Excel4 macro sheet
will behave as if was a regular worksheet. This should not cause a problem
in most cases, but if you have entries in cells that correspond to the
cell ranges you have chosen for consolidation, they will be used in the
formulas.

   c) "Incorrect" result from formulas

The z-relative arrays contain the same number of items as the number of
worksheets in your workbook, and that includes the consolidation worksheet.
As such, the COUNTA function will always return that number when used with
the cCell (and similar) arrays. The value zero is returned from empty cells
and so the COUNT function will count those cells. For the same reason, the 
SMALL, AVERAGE AND MIN functions may not return the expected answer.  Thus, 
it is recommended that these functions not be used in the consolidation 
formulas, unless you are sure that each worksheet for a specified cell 
contains an entry. 

   d) Only returns values 

These formulas have been constructed to return only arrays of values. This
was done by design, since consolidation is performed on numbers. All text 
entries are converted to zero. However, if you would prefer a solution
that does include text entries in the arrays, follow these steps:

Define nCell as =N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN())))
Define tCell as =T(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN())))
Define cCell as =IF(ISERROR(nCell),"",IF(tCell<>"",tCell,nCell))

Of course you would need to do this for the offset arrays as well. I leave
that as an exercise to the reader.
