Forum Discussion
Dynamic Let Function
I don't think there is any elegant way of collecting all the LET variables together so that any one may be selected using a formula. CHOOSE is a good option but relies upon the developer inserting a list of parameters that correspond to the variables used within the LET function. The question then is, would the CHOOSE statement remain once the formula is debugged or should it be removed?
Another approach that I have started playing with is to build a data dictionary, comprising pairs of values, the first being a variable name (text) and the second its value, as a thunk (a function that is evaluated by providing it with an empty parameter string).
Working with Arrays of Ranges | Microsoft Community Hub
My intention was to allow a LAMBDA function to return two or more arrays that can be referenced as required for further calculation or presentation. The type of situation I had in mind was a function that returns an array of 'opening cash' amounts and the 'opening revolver debt'.
The relevance to this discussion is that it would be possible to load all the variables used within a LET function to such a data dictionary (possible, but not necessarily pretty) and, from there, examined by name or by index.
Many thanks Peter.
"The question then is, would the CHOOSE statement remain once the formula is debugged or should it be removed." You could remove the CHOOSE statement but there is no need especially if the goal isn't simply to debug. You may want to pull out different bits of information from this let function based on changing parameters in your spreadsheet.
Your thunk idea is very intriguing.