Excel Tip of the Day
Simplify summary formulas with 3-D range references (Excel 97/2000/2001/2002)
It's common to set up workbooks so that data is logically split across several sheets. For example, you may dedicate worksheets to individual months or quarters. When you use this organization technique, you often also create a summary sheet that consolidates the information. If so, you may wind up creating a lot of long, cumbersome formulas, such as:
=SUM(January!D38+February!D38+March!D38+April!D38+May!D38+June!D38+July!D38+August!D38+September!D38+October!D38+November!D38+December!D38)
There's a much more elegant way to create such a summary--use a 3-D range reference. With 3-D references, the worksheets form the third "dimension" of the reference. When you create the reference, the first element is the range of worksheet names. You simply specify the first and last worksheet, separated by a colon.
For instance, the previously shown formula can be rewritten as:
=SUM(January:December!D38)
to achieve the same result.
(Content provided by Element K Journals)
(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
Print
CloseCopyright © 2001 - 2025 MJ Technologies, LLC. All rights reserved.