EXCEL 3D – Easy as 1-2-3 and A-B-C and Others

EXCEL 3D – Easy as 1-2-3 and A-B-C and Others

By David Hager

A number of years ago (~16) Laurent Longre discovered that Excel formulas that calculated over 3D ranges could be made by using the INDIRECT function(see Issue #3).

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

This formula was developed out of necessity, since many of the functions in Excel do not work with Excel’s built-in 3D cell range notation.

This methodology was refined to formulas of the general form:

=SUMPRODUCT(XXXIF(INDIRECT(“‘”&SOMETHING&”‘!cell_range”),criteria))

In its basic form, it was first developed sometime in the 2004-2005 time period, but it is unclear who was actually the first person to derive the “final” form.

A specific example of this

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&SOMETHING&”‘!B2:F3”),”<>”&””))

This article is not actually about how to use these 3D formulas. Rather, it is about the formula techniques that can be used to create multi-sheet range in the SOMETHING part of the formula.

In each example shown here, an actual formula/range can be used, or a defined name representing the formula/range can be used.  In the following example, an array created by the ROW function can be used to return sheet tabs named as single digit numbers.

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&ROW($1:$4)&”‘!B2:F3”),”<>”&””))

Or in the defined name form:

MyNumberTab1 =ROW($1:$4)

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&MyNumberTab1&”‘!B2:F3”),”<>”&””))

It is important to note that this type of 3D formula has another inherent advantage over Excel’s normal 3D range formulas (i.e. – =SUM($1:$4!B2:F2). The tabs do not have to be contiguous for the formulas to function as intended/desired. If, for example, the sheet containing the 3D formulas was “between” sheet “1” and “3” and had entries in the cell range of that sheet, they would be included in the sum for an Excel-inherent 3D cell reference, but they are not for the INDIRECT 3D reference form.

Another formula returns an array of single letters from a-z.

=CHAR(ROW($97:$122))

So, using this in the INDIRECT formula will return the expected values for the specified formula from all sheets named a-z (26 sheets). Note that all 26 sheets have to exist for the formula to not return an error. Of course, this formula can be modified for an alphabet subset if necessary.

Another formula returns an array of months of the year, which is another popular choice for naming worksheet tabs.

=TEXT(ROW($1:$12)&”-1″,”MMM”)

All of the formula arrays mentioned in this article can be modified by concatenating a text string to the array. In this case, the following array returns 12 strings, starting with “Jan 2011”.

Months2011 =TEXT(ROW($1:$12)&”-1″,”MMM”)&” 2011″

The final example illustrates the simplest form of an array to be used: a simple cell range.

=$A$1:$A$4

Two modifications of this can be useful as well. The OFFSET function could be used to create a dynamic cell range that would expand or contract based on user input.

For cases where the name list is anticipated to be static, the cell range can be evaluated in the formula bar and the resulting array of tab names can be stored as a defined name.

MyTabs1={“John”;”Paul”;”George”;”Ringo”}

I hope that you found this discussion useful.

Advertisements

3 thoughts on “EXCEL 3D – Easy as 1-2-3 and A-B-C and Others

  1. Lori

    This was a cool trick and one i still often see being used on forums. In the EEE003 link a formula is given for pulling an array of numbers from different sheets:

    =N(INDIRECT(“Sheet”&”!”&ADDRESS({1,2,3},{1,2,3})))

    For text we can use T(.) in place of N(.) but how about for mixed data eg {1,”a”,TRUE} ?

    I believe this is something that was previously not thought possible but a solution has recently come to light based on some developments at the Excelxor blog:

    =CELL(“contents”,INDEX(TRANSPOSE(INDIRECT(“Sheet”&”!”&ADDRESS({1;2;3},{1;2;3}))),))

    So the required construction requires three additional functions to dereference the array!

    Reply
  2. Pingback: Using the SUBTOTAL Function in #Excel To Aggregate Values From Multiple Filtered Lists by David Hager | Excel For You

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