SOLVED

GENERATE LIST BASED ON CELL VALUE

%3CLINGO-SUB%20id%3D%22lingo-sub-2408112%22%20slang%3D%22en-US%22%3EGENERATE%20LIST%20BASED%20ON%20CELL%20VALUE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2408112%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20with%20the%20following%2C%20I%20don't%20know%20if%20my%20wording%20is%20correct%20but%20I%20am%20trying%20to%20achieve%20the%20following%3A%3C%2FP%3E%3CP%3ESheet1%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22320%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3ETicket%20nr.%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EJames%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3ERonell%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EGreg%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EMia%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E2%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E3%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E4%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E5%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E6%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E7%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E8%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E9%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EX%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ENow%20I'm%20trying%20to%20open%20a%20sheet%20for%20each%20person%20with%20a%20list%20of%20ticket%20numbers%20needing%20their%20attention.%3C%2FP%3E%3CP%3Esheet2%20(James)%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EJames%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E2%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E5%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E6%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E8%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esheet3%20(Ronell)%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3ERonell%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E1%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E4%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E8%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esheet%204%20(Greg)%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EGreg%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E2%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E3%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E6%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E9%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esheet5%20(Mia)%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2264%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2252%22%20height%3D%2214%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EMia%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2252%22%20height%3D%2214%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E5%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2252%22%20height%3D%2214%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E7%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2252%22%20height%3D%2214%22%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20this%20possible%3F%20Best%20I%20can%20describe%20a%20reversed%20kind%20of%20vlookup.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2408112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2409758%22%20slang%3D%22en-US%22%3ERe%3A%20GENERATE%20LIST%20BASED%20ON%20CELL%20VALUE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2409758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1069273%22%20target%3D%22_blank%22%3E%40RiaDreyer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20one%20solution.%20It%20requires%20the%20most%20recent%20version%20of%20Excel%2C%20as%20it%20makes%20use%20of%20the%20Dynamic%20Array%20function%20FILTER%20as%20well%20as%20the%20function%20INDIRECT%20in%20order%20to%20write%20just%20a%20single%20formula%20that%20could%20be%20copied%20across%20various%20columns.%3C%2FP%3E%3CP%3EHere's%20the%20simpler%20version%20of%20the%20formula%3A%20%3CSTRONG%3E%3DFILTER(Table1%5BTicket%20nr.%5D%2CTable1%5BJames%5D%3D%22X%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20here's%20the%20more%20versatile%3A%20%3CSTRONG%3E%3DFILTER(Table1%5BTicket%20nr.%5D%2CINDIRECT(%22Table1%5B%22%26amp%3BI1%26amp%3B%22%5D%22)%3D%22X%22)%3C%2FSTRONG%3E%20where%20cell%20I1%20contains%20the%20value%20%22James%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20created%20your%20table%20as%20an%20official%20%22Excel%20Table%22%20to%20make%20it%20easier.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

 

Please help me with the following, I don't know if my wording is correct but I am trying to achieve the following:

Sheet1 looks like this:

Ticket nr.JamesRonellGregMia
1 X  
2X X 
3  X 
4 X  
5X  X
6X X 
7   X
8XX  
9  X 
10   X

Now I'm trying to open a sheet for each person with a list of ticket numbers needing their attention.

sheet2 (James)

James
2
5
6
8

 

sheet3 (Ronell)

Ronell
1
4
8

 

sheet 4 (Greg)

Greg
2
3
6
9

 

 

sheet5 (Mia)

Mia
5
7
10

 

is this possible? Best I can describe a reversed kind of vlookup.

2 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@RiaDreyer 

 

I've attached one solution. It requires the most recent version of Excel, as it makes use of the Dynamic Array function FILTER as well as the function INDIRECT in order to write just a single formula that could be copied across various columns.

Here's the simpler version of the formula: =FILTER(Table1[Ticket nr.],Table1[James]="X")

 

And here's the more versatile: =FILTER(Table1[Ticket nr.],INDIRECT("Table1["&I1&"]")="X") where cell I1 contains the value "James"

 

I also created your table as an official "Excel Table" to make it easier.

 

 

thank you so very much!!!

 

this was most helpful