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 ). 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.