Forum Discussion
Would a FOR.EACH function be useful in Excel 365?
The attached files are based upon a workbook that emulates the proposed function using a VBA macro. There would appear to be many potential applications in situations where the @operator needs to be used to restrict the operation of an array parameter.
Intro sheet
9 Replies
Still working. the present sheet was written as a demonstration of LET relevant to a question on Chandoo. The last entry for each field of a table was returned using XLOOKUP. It is not possible to search multiple rows within a single array formula, so the column is picked up as a relative reference.
Wrapping the formula within FOR.EACH removes the need for relative referencing and returns the results as a dynamic array.
FOREACH replaces relative referencing
Whereas the previous workbooks were created to demonstrate and idea and put it forward for discussion, this time I simply built the formula around an existing solution. The implementation of the macro is still weak but the idea seems to work well.
PeterBartholomew1what are you working on
What I am trying to develop is an approach to building financial or engineering models that is based on the use of dynamic arrays in its entirety. Any input data would be entered into Tables in order to provide structured references that are inherently dynamic. From there, the entire solution should respond to change without any user intervention or any need to create 'oversized' formula ranges.
Other goals are to get rid of the concept direct cell referencing totally (the location on the sheet should be of no more relevance than the position of text on a page, - order matters but not the location). Another advantage is that the concept of relative referencing can also be consigned to the trash can of history; it never was a meaningful concept though it did fit an amateurish way of working that was built on the idea of replicating formulas, rather than recognising arrays and lists as objects.
Whether this is a good place to express the idea that traditional spreadsheet concepts are junk is another matter!
I have attached an updated file to the previous post which allows for formulae that return 1D arrays, giving an array of arrays held within a 2D spilt range. Comments would be welcome!
