Populate Worksheet # 2 based on Filtered Worksheet # 1 Data

I have a worksheet # 1 with the following type of data:

FIRST NAME LAST NAME DEPARTMENT HIRE DATE STATUS
John Smith Marketing 01/01/10 Active
Jim Smith Sales 01/15/12 Active
Susan Johnson Marketing 06/15/12 Active
John James Accounting 07/14/14 Inactive

I want worksheet # 2 to be automatically populated will all rows from worksheet # 1 where the department = Marketing.

So the output would be:

FIRST NAME LAST NAME HIRE DATE STATUS
John Smith 01/01/10 Active
Susan Johnson 06/15/12 Active

Need help with the function to automatically populate worksheet # 2 in this fashion.

Thanks.

Rich Parkinson

I added an excel file with data in it if that helps.

The function is not working for me. See the attached.

I have 2 worksheets. I used the function exactly as you described and it is giving me incorrect results.

Let me know is anyone has a solution.

Thank you.