# Advanced Formula Challenge #13: Single Array Containing All Entries From a Given Range in Multiple Worksheets 22

The challenge this week is as follows: a single formula to return an array which consists of all entries (of potentially mixed datatype) from a given range in multiple worksheets.

No restrictions are placed on the dimensions of the returned array.

For example, using the attached file (download here), the formula should return an array such as:

`{18,"",19,63,"","",67;"",46,"","","L","",7;"N","Z","","F",70,19,"";"","","","","","",73;"","","T","","",88,"Y";"U","","B","F","Q",71,"";"","H","U","R",86,84,"Q";11,"R",84,"","",43,72;39,53,"","","R",60,80;"","V","B",33,"",46,39;"","B","","","V","","";"P","","M","","","",85;"O","",9,"","","","";"","I",9,"","S","S",59;"","X","J","","","","X";46,72,"",22,"",67,"P";35,32,69,"O","","","V";24,"","","","R","","";"",5,"B",69,"C","","";"P","A",19,"","","K","B"}`

or a dimensional variation thereof.

For the sake of consistency, the specified range (A1:G4 in the attached example) should be referred to as the Defined Name Rng in any formulas. Likewise for any references which refer to a collection of worksheets, e.g.:

`={"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}`

which should be given the Defined Name Sheets.

Also please note that this is not a Shortest Formula Challenge! Hence rigour should be preferred over brevity in any solution.

Solution next week. Good luck!

1. Alex Groberman says:

Great challenge!

Here goes:

`=INDIRECT(SUBSTITUTE(T(INDEX(Sheets&"!R"&MODE.MULT(ROW(Rng)+("."&COLUMN(Rng)),ROW(Rng)+("."&COLUMN(Rng))),)),".","C"),)`

The “evaluate formula” may show errors but it will return the correct results if wrapped around SUM or COUNTA as examples.

2. Alex Groberman says:

On second thought my formula won’t work with any column number ending in zero (e.g. column 10). Here’s another shot at it:

`=INDIRECT(T(INDEX(Sheets&"!R"&MOD(ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1,ROWS(Rng))+1&"C"&INT((ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1)/ROWS(Rng))+1,)),)`
3. lori says:

i like Alex’s approach, but think it might need wrapping in IF(1,CELL(“contents”,…)) for it to work inside functions like COUNT and MEDIAN and also to be able to array enter over a range of cells.

A 2013 variation that i believe will also work in different language versions is:

`=CELL("contents",IF(1,+INDIRECT("'"&Sheets&"'!"&BASE(MODE.MULT(DECIMAL(ADDRESS(ROW(Rng),COLUMN(Rng),4),36),DECIMAL(ADDRESS(ROW(Rng),COLUMN(Rng),4),36)),36))))`
4. Alex Groberman says:

Wow lori, that is fantastic! It never occurred to me that any cell reference could be identified numerically using base 36. This opens up a ton of possibilities!

-Alex

5. Alex Groberman says:

Also of note, this DECIMAL technique will work for all rows up to column ZZ, or for all columns up to row 999,999.

For AAA1000000 to XFD1048576, Excel’s precision isn’t high enough. Still, a very good working domain overall!

-Alex

6. lori says:

alex – according to MS help on the DECIMAL function it supports all numbers up to 2^53 (which is consistent with the standard floating point double representation). Since this is greater than 36^10 this should support any cell reference up to ten digits including XFD1048756 I believe but I haven’t had a chance conclusively test this.

7. Alex Groberman says:

Adapting lori’s formula to a pre-2013 solution:

`=CELL("contents",IF(1,+INDIRECT("'"&Sheets&"'!"&ADDRESS(INT((ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1)/COLUMNS(Rng))+MIN(ROW(Rng)),MOD(ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1,COLUMNS(Rng))+MIN(COLUMN(Rng))))))`

This should return identical results to the solution using DECIMAL.

8. Alex Groberman says:

You’re right (as always!) lori, I was fooled because for example the formula

`=DECIMAL("XFD1048576",36)`

Shows a result in the cell of 3394816136683600, and I misinterpreted the rounding of the visible result to mean that Excel was losing precision. But having then translated that result back into base 36, I can confirm it returned the correct answer.

9. lori says:

Yeah, applying BASE(…,10) to the formula result shows it actually ends in “02”. This display rounding can be a source of much confusion for users, for example entering 1 in A1 and =1+1E-15 in B1 we see that both return the same display result, also putting =A1-B1 in C1 returns zero, it is only by placing parentheses around the formula =(A1-B1) that we can see the underlying numbers are different.

And perhaps it might be worth adapting the pre-2013 formula to use OFFSET which i understand is a little more efficient:

`=CELL("contents",IF(1,+OFFSET(INDIRECT(ADDRESS(MIN(ROW(Rng)),MIN(COLUMN(Rng)),,,Sheets)),INT((ROW(INDIRECT("1:"&COUNTA(+Rng)))-1)/COLUMNS(Rng)),MOD(ROW(INDIRECT("1:"&COUNTA(+Rng)))-1,COLUMNS(Rng)))))`

(though still less readable!) Maybe xor lx had something like this in mind – we shall see…

10. MichaelCH says:
`=INDIRECT(INDEX(Sheets,N(INDEX(MOD(ROW(OFFSET(A1,,,ROWS(Rng)*COUNTA(Sheets)))-1,COUNTA(Sheets))+1,)))&"!"&ADDRESS(INDEX(ROW(Rng),N(INDEX((ROW(OFFSET(A1,,,ROWS(Rng)*COUNTA(Sheets)))-1)/COUNTA(Sheets)+1,))),COLUMN(Rng)))`
11. MichaelCH says:
`=INDIRECT(T(INDEX(Sheets&TEXT(MODE.MULT(ROW(Rng)*10^5+COLUMN(Rng),ROW(Rng)*10^5+COLUMN(Rng)),"!R0C00000"),)),)`
12. Alex Groberman says:

Wow MichaelCH, that formula is tight! I think you still need to wrap it in CELL(“contents”,…) though for the reasons Lori outlined above. But outrageous nonetheless!

13. MichaelCH says:

With CELL():

`=CELL("contents",IF(1,+INDIRECT(Sheets&TEXT(MODE.MULT(ROW(Rng)*10^5+COLUMN(Rng),ROW(Rng)*10^5+COLUMN(Rng)),"!R0C00000"),)))`
14. Dave Wenta says:

XOR LX, May we take some “liberties” with a rule. Specifically may

`={"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}`

be defined as a column?

`={"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5"}`

Dave

15. @Dave Wenta

I have no problems with that.

Regards

16. David N says:

Lori, Alex, and/or Michael,

Assume each tab had an additional column whose values should serve as criteria for a count, sum, average, min, max, etc. Can you show how your formulas might be adapted to incorporate that logic?

I am thinking along the lines of the XOR LX post from Jan 8, 2016 — Incrementing Indirect Column References Within SUMIF(S)/COUNTIF(S) — but of course needing to account for this case involving the data range and criteria range being of different sizes.

17. And, you can use these solutions in conjunction with the Excel 2016 TEXTJOIN function to make delimited strings (among other things)for multi-sheet ranges.

18. lori says:

Probably worth mentioning for reference that in Office 365 one can convert the 3D range directly to a vertical array without even needing the list of sheet names:

`=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",0,Sheet1:Sheet5!A1:G4)&"</b></a>","//b"),"")`

(Credit to D.Hager & B.Jelen in follow up post to link above)

This site uses Akismet to reduce spam. Learn how your comment data is processed.