Created by Laurent Longre
The problem – to make a 3D worksheet array formula. What this means is to
create an array representing a z-range (a range across worksheets) that
evaluates in the formula bar as an array. The 3D range used in Excel, i.e.
does not behave that way. I suspect that nearly everyone on the EEE list
has tried to do this and found that it was not possible. However, Laurent
found that it was possible, given some advanced formula tricks. The INDIRECT
function can return a 3D reference if it is operated on by the N function.
An illustration of this type of formula is shown below.
3D Diagonal Formula –
returns the sum of Sheet1!A1, Sheet2!B2 and Sheet3!C3. How does it work?
“Sheet”&{1,2,3}&”!”&ADDRESS({1,2,3},{1,2,3}) evaluates to the array of
strings.
{“Sheet1!$A$1?,”Sheet2!$B$2?,”Sheet3!$C$3?}
When the INDIRECT function operates on this array, the expected array of
values appear (by highlighting in the formula bar and pressing F9), but
for some reason this array cannot be used by Excel functions. The use of
the N function creates an array that can be used, so that the SUM function
returns the desired result.
Dick,
I’ve been coming here for a while and enjoy the readings. I’m not at the level of most of the folks that post here, but I am curious about this one. How is this different than =SUM($A$1)+SUM(Sheet2!$B$1)+SUM(Sheet3!$C$1). Wouldn’t they return the same values?
I’m probably just not seeing the big picture of the use of this type of formula…
-Steve
Steve – It’s true that for the example given a simple summation formula does the job, but the point is that this method extends to larger selections.
TO see this you can replace {1,2,3} by row(1:10) to sum across 10 sheets, or define the name ‘AllSheets’ as ‘=get.workbook(1)’ to refer to all sheets in a workbook.
This 3D-type functionality also works with other functions than just n and t: subtotal,countif,sumif and database-functions also allow arrays as references.
So, just to ask. How would I then create a sumproduct across two 3-d arrays using this approach.
How about true 3d array operations. I have 400 spreadsheets 100 by 20 that I need to slice and dice.
I’m not quite there. How come =SUM(N(INDIRECT(“Sheet”&”!”&ADDRESS({8,8,8},{7,7,7})&”:”&ADDRESS({9,9,9},{8,8,8})))) does not evaluate to the same as =SUM(Sheet1:Sheet3!G8:H9)? It looks like N is just taking the first element of each range and the sum is operating on those. I’m not having any luch nesting another SUM. Any ideas?
Brett
=SUM(SUBTOTAL(9,(INDIRECT(“Sheet”&”!”&ADDRESS({8,8,8},{7,7,7})&”:”&ADDRESS({9,9,9},{8,8,8}))))) works. (Thanks, Lori)
Brett
With a bit of tweeking this should work: http://contextures.com/PivotMultConsol.zip
//Ola
I am trying to use Excel to sort club officers. Each club gets a sheet. How can I sort and report on the same position across the sheets? I assume this is an array, something I vaguely remember from programming about 30 year ago! Thanks in advance. Michael
Dick, you’re awesome!!!
Thanks a lot for this tip, it made my professional life much easier :)