Extract location of staff from one sheet to another using a formula.

[soul1974]

Morning All,

I have a list of my staff's current locations and I want to extract a specific location based on user input from a dropdown list and add it to a new list.

Sheet 1 contains the location of all staff, while Sheet 2 should display only the staff based on the selected location. I have tried various methods, but have been unsuccessful.

 

[vletm]

soul1974​

=FILTER(FILTER(Sheet1!A3:C20,Sheet1!C3:C20=Sheet2!E1,""),{1,0,0})

 

[p45cal]

=FILTER(FILTER(Sheet1!A3:C20,Sheet1!C3:C20=Sheet2!E1,""),{1,0,0})​

Why 2 filters?
Doesn't this do the same?:

=FILTER(Sheet1!A3:A20,Sheet1!C3:C20=Sheet2!E1,"")

 

[vletm]

p45cal​

because I've used Sheet1!A3:C20
and soon he would like to get something else...

 

[soul1974]

Thank you for your assistance. Both options are working great and I appreciate the additional features. Do you know where I can learn more about the formulas you used?

 

[vletm]

Have You seen?

Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Chandoo.org makes you awesome in Excel & Power BI. Learn all about data analysis, visualization, reporting, business dashboards, automation, macros, VBA, power query, power pivot, Excel functions, formulas, pivot tables and Power BI for free. Tutorials, templates, sample files, code examples...

chandoo.org