Forum Discussion
Help with displaying specific columns based on user selection
Hi All, I need help with displaying specific columns in excel when selecting an item from a list within a table. Here is an image of the table: If I was to select an option in the list, ...
All sorts of solutions are possible and depend somewhat on the arrangement of your data. For example, do the columns to return form contiguous ranges? If so, DROP and TAKE would provide a solution in the form of a range reference, which is useful if you wish to use SUMIFS etc.
In the formula below, I have used SWITCH to return an array of column indices for each form type,
= LET(
tableIndices,
SWITCH(formType,
"Standard", {2,3,4},
"Exchange", {5,8},
"Return", {1,6,7,9}),
CHOOSECOLS(table, tableIndices)
)This formula would return a dynamic array.
A further step towards making every formula a Lambda function would be to use the formula above to define the function 'ReturnFormλ', in which case the worksheet formula could be
= ReturnFormλ(table, formType)with all the 'nuts and bolts' decently out of sight!