Forum Discussion
Formulas Challenge for 365
I have attempted part 1 of your challenge but not within the rules. I only use defined names to reference data (at one time I ran macros to delete the content of any cell that contained a direct reference1).
My approach was to create an array of Thunks, each element containing the data from one of the tables.
Since submitting, I have had a look at Riny_van_Eekelen 's solution and may have some reconsidering to do. I had not realised that VSTACK works over a 3D range reference! It does mean that the tables have to be aligned but often they are.
- This is a fascinating approach! Thank you for sharing. With access to all these functions, this is essentially vba at the sheet level but much more accessible (DROP/TAKE are the equivalents to .resize, etc.). I was testing VSTACK the other day and was surprised to see it (and HSTACK) support 3D references. Very cool.
This page will have to be updated soon:
https://support.microsoft.com/en-us/office/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6"VSTACK ... support 3D references. Very cool."
Yes, that was quite a discovery. Stay in touch over the results of further exploration. Although this forum is largely devoted to Q&A, I would like to see more discussion and sharing of experience.
You can see from my response that I am not fully committed to the shortest formula concept, including 44 spaces and 6 line feeds in a formula of length 123 definitely rates a 'could do better'! I have also used thunks in the Max problem as a device for getting 2 row arrays through MAP. The more obvious BYROW does not allow multiple parameters.
The final version of you Max problem is more comfortable for me because I am used to picking out arrays from multidimensional layouts.
As for your comment regarding VBA, last week on another forum, I wrote a formula which turned out to be needed because an add-in had ceased to work. The places where I would still need VBA include writing event handlers or using the object model to interrogate relationships between shape objects. Most calculation would appear to be better achieved through the use of worksheet formulae.
- Admittedly, the shortest formula approach may not be the best if taking into account sustainability and being able to use the formula in more workbooks. I've been looking for situations to create universal LAMBDAs that I can use again and again. I've gone back to some old vba exercises to see if I could make LAMBDAs do the same thing as vba. I've run into the dreaded 'array of arrays' limitation (TEXTSPLIT, naturally) when trying to write a LAMBDA to un-pivot some data. I believe it's possible with INDEX (and maybe OFFSET) but not worth the trouble if PowerQuery can do it in a snap.
