Forum Discussion

CharlieP2k's avatar
CharlieP2k
Copper Contributor
May 25, 2023

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, say for example "Return" I want to generate a table with 6 Columns. However, if I selected "Standard Allocation" I want 4 different columns. All the data is retrieved from a table called "Data_Dump" that gets update every time a MS Form is filled out. Below are some images that illustrate this idea.

For example:
Selecting Standard Allocation show this-


Selecting Returns shows this -


I tried doing this in Power Query (if that is the correct feature) but I wasn't able to get anywhere. Is this possible to achieve in excel with the provided features? if so please let me know!

Any help is appreciated!

excel
Formulas and Functions

3 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 25, 2023

    CharlieP2k 

    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.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      May 25, 2023

      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!

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    May 25, 2023
    This can probably be pulled off using the FILTER function in combination with HSTACK. Which version of Excel are you using?
    Can you upload an anonymized copy of your file by any chance?
    All we need is a made-up file with the relevant tables (using the same table and column names), but with a couple of rows of dummy data.

Resources