Monthly Archives: April 2016

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

Advanced Formula Challenges

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!

Update: Formula Wrapping Issue in Firefox and Google Chrome 1

Uncategorized

I have now put in place a solution which means that the entire contents of a given formula should be visible in all cases, i.e. those contents do not extend off the edge of the screen. This should have fixed the issue not only in Google Chrome, but hopefully also in Firefox.

The resulting formatting is not quite as I would have wished, though it at least means that posts are no longer rendered unreadable.

I would just like to add a belated thanks to Vaibhav, whose suggestion many months ago has proved to be extremely useful. So thank you, Vaibhav, and sincere apologies for not having resolved this issue for my Firefox readers until now.

Regards

Apologies to Readers: Formula Wrapping Issue in Firefox and Google Chrome 2

Uncategorized

It has just come to my attention that, due to a recent update in Google Chrome, this browser no longer supports the particular CSS word-wrap property which is a feature of all formulas posted on this site.

As such, any formulas viewed in that browser, or in Firefox, will not be wrapped and, as such, those beyond a certain length will not be viewable.

I understand that this issue has always been present for readers who use Firefox (thanks to Bert for bringing this to my attention). I also appreciate that this means that the vast majority of my posts will lose their appeal due to their unintelligibility, for which I can only apologise.

I am currently working on a solution to this issue and will hopefully have a fix soon.

Regards