Forum Discussion

RiaDreyer's avatar
RiaDreyer
Copper Contributor
Jun 02, 2021
Solved

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 ...
excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Jun 02, 2021

    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.

     

     

mathetes's avatar
mathetes
Silver Contributor
Jun 02, 2021

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.

 

 

  • RiaDreyer's avatar
    RiaDreyer
    Copper Contributor
    Jun 03, 2021

    thank you so very much!!!

     

    this was most helpful

Resources