Forum Discussion
GENERATE LIST BASED ON CELL VALUE
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.
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.
2 Replies
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
