Display a list of columns if data is present within?

ExcelHelpNecessary

New member
Joined
Jan 14, 2021
Messages
5
Reaction score
0
Points
0
Excel Version(s)
2016
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!


 

Attachments

  • Sample.xlsx
    15 KB · Views: 15
Last edited by a moderator:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
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?)
 

Attachments

  • ExcelGuru10999Sample.xlsx
    16.8 KB · Views: 16
Last edited:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
365
See if this version gets you what you want (I think CONCAT was available in Excel 2016):
 

Attachments

  • ExcelGuru10999Sample v02.xlsx
    16.6 KB · Views: 19

ExcelHelpNecessary

New member
Joined
Jan 14, 2021
Messages
5
Reaction score
0
Points
0
Excel Version(s)
2016
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 [FONT=&quot]CONCATENATE? I tried subbing it in but it's not working correctly.[/FONT]
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
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:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
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.
 

Attachments

  • ExcelGuru10999Sample v04.xls
    35.5 KB · Views: 15
Last edited:
Top