I am not an experienced Excel user, so I was hoping to find some help here.
I have a sheet containing the names of reports, the type of report (PDF or Excel), and a list of recipients. See example below:
| Title | Type | Recipients of Report |
|---|---|---|
| Weekly Sales | John Doe, Jane Doe | |
| Monthly Sales | John Doe, Jane Doe, Carl Smith |
I want to be able to pick a person from a drop down list (or input field) and see the reports that are sent to this person.
Is there any smart way to do this?
Thank you for helping!
I have tried to Google a way to do this, but as mentioned, I am new to Excel and I have no clue how to figure this out. Any tips/help is more than welcome!
CodePudding user response：
So a quick attempt, without using FILTER():
Cells I2 to I5 is the unique list that the data validation in cellE2 uses. If find() gets a number then if() returns the name, if find() does not find the name it produces an error so iferror() changes that to zero.
So you can copy instead of typing:
=IF(IFERROR(FIND(E$2,B2,1),0)>0,A2,"")
Then I would do a concatenate to produce a list based on results in column C that are not blank, and put that in cell F2. Something like:
=substitute(A2&", "&A3&", "&A4&", "&A5&", "&A6,", , ",", ")
which will work for the range A2:A6. You can extend that.
CodePudding user response：
FILTER() with SEARCH() may work.
=FILTER(A2:A3,ISNUMBER(SEARCH(F1,C2:C3)))
