Excel For You | For the Excel community – by David Hager



#Excel Magic Consolidator by David HagerI made this a long time ago. However, you still might find it useful, if only for the examples of the formulas used.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. Be aware that this methodology uses xlm in defined name formulas. Therefore, do not change the file type. Read all instructions before using.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 atany position within the workbook. There is no VBA or xlm macro code usedin this solution. All of the work is done by defined name formulas. b) Writing the formulasAs an example, if you type the formula =SUM(cCell) in cell B4 on the Summaryworksheet, that formula will return the sum of cell B4 for every worksheetin the workbook, since cCell as used in cell B4 returns the array of entriesfor 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 nameTo 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 workbookTo 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 benamed MagicCons.xls). Note that a new workbook must first be saved for thistechnique to work.2) How does it work? a) Understanding the formulasAll of the formulas used to create the consolidation are defined nameformulas. 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 beused as the consolidation worksheet. ThisSheet is defined as:=LEFT(GET.DOCUMENT(1),FIND("]",GET.DOCUMENT(1)))&TheSummarySheetNameThis formula returns the sheet name of the consolidation worksheet in the form "[MagicCons.xls]Summary". This string will be different if used inanother 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 orz-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 ellDown 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 willhave 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 functionsThere are quite a few Excel functions that do not work with the 3D rangesthat are inherent to Excel. For example, the MATCH function cannot be asshown 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 givean array that the MATCH function can operate on. The latter case containsthe 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 usedjust like any normal range is used in an array formula.3) Problems a) Circular referencesIf you try to use the consolidation formulas on any other worksheet thanthe designated consolidation sheet, a circular reference will be created.Do not use these formulas on other worksheets! b) Sheets other than worksheetsThe presence of charts do not interfere with theworkings of the consolidation formulas. However, an Excel4 macro sheetwill behave as if was a regular worksheet. This should not cause a problemin most cases, but if you have entries in cells that correspond to thecell ranges you have chosen for consolidation, they will be used in theformulas. c) "Incorrect" result from formulasThe z-relative arrays contain the same number of items as the number ofworksheets in your workbook, and that includes the consolidation worksheet.As such, the COUNTA function will always return that number when used withthe cCell (and similar) arrays. The value zero is returned from empty cellsand 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. Thiswas done by design, since consolidation is performed on numbers. All text entries are converted to zero. However, if you would prefer a solutionthat 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 leavethat as an exercise to the reader. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download