GENERATE LIST BASED ON CELL VALUE

[RiaDreyer]

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.	James	Ronell	Greg	Mia
1		X
2	X		X
3			X
4		X
5	X			X
6	X		X
7				X
8	X	X
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 · Accepted 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.

[#3]

thank you so very much!!!

this was most helpful