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!