Results 1 to 7 of 7

Thread: Display a list of columns if data is present within?

  1. #1
    Neophyte ExcelHelpNecessary's Avatar
    Join Date
    Jan 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Display a list of columns if data is present within?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a table with the following data:

    Rows: Names of People
    Columns: Fruits

    Is there anyway to set up a dropdown list with all the names, then in a listing display any columns next to their name with data inside?

    ie: there are 6 columns (orange, apple, banana, grape, pear, melon) and Clarence has "yes" under the orange, banana and pear columns. How can I set up a dropdown so it shows all the names, and when you click Clarence it shows, in a list, like such:

    Orange
    Banana
    Pear

    Excel 2016 please

    I've attached a sample workbook.

    Thanks for any help!


    Attached Files Attached Files
    Last edited by p45cal; 2021-01-21 at 05:07 PM.

  2. #2

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    See if the attached works as you want…

    Edit post posting, shucks, just seen you are Excel 2016. Hang on a bit. (Are macros out of the question?)
    Attached Files Attached Files
    Last edited by p45cal; 2021-01-21 at 05:05 PM.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    See if this version gets you what you want (I think CONCAT was available in Excel 2016):
    Attached Files Attached Files

  5. #5
    Neophyte ExcelHelpNecessary's Avatar
    Join Date
    Jan 2021
    Posts
    4
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by p45cal View Post
    See if this version gets you what you want (I think CONCAT was available in Excel 2016):
    Throwing a #NAME error, I don't think CONCAT is available. Are we able to use CONCATENATE? I tried subbing it in but it's not working correctly.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    For some reason, I'm not getting alerts to your replies, so sorry for the delay, I just came across this by chance.
    I'm trying to find a solution by going back to Excel 2003; while I try, would a user-defined function (UDF) be acceptable? I can write one that duplicates the CONCAT function, so that when you ultimately upgrade to a version of Excel that includes CONCAT it should be fairly seamless to transfer as all you'd need to do is to delete the UDF. A UDF is a macro function so macros would have to be enabled.

    ps. from all my exploration CONCAT is available in Excel 2016. Is your version of Excel really 2016, and is it as up to date as it can be?
    Last edited by p45cal; 2021-01-28 at 01:27 PM.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,889
    Articles
    0
    Excel Version
    365
    The attached workbook is Excel 2003.
    Cell E3 has a similar formula as suggested earlier but uses a UDF version of CONCAT
    Alternatively, using only built-in functions the cell E5 has another formula which uses helper cells G5:L5 whose formula is array-entered (using Ctrl+Shift+Enter rather then just Enter).

    Being an Excel 2003 file there are no slicers so you you have to go to the dropown at cell A2 of Sheet2. You should be able to cobble something together from this in your more recent version of Excel.
    Attached Files Attached Files
    Last edited by p45cal; 2021-01-28 at 05:26 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •