Excel Formula for filtering data from one sheet to another

microsoft excelworksheet-function

I have table that has the following columns (Agent Name, Client, Policy #, Premium) with the name of agents repeating several times since they have multiple clients.

I don't want to give all the Agents the information from the other Agents.
I know how to filter data within a table but what I want is the formula to have a second worksheet that filters for a specific agent on the original sheet. I tried using =Vlookup, but since there are repeating values in column B, it comes back with errors.

What I want is to have is master table with "All" the info, and then separate worksheets with filtered data for each individual Agent (one for Agent A, another for Agent B, and so on).

Best Answer

I suggest using a PivotTable with AgentName as the Report Filter:

SU569607 example

If Agents have so many clients that the width would be a problem, drag Client from Column Labels to Row Labels and change Design > Report Layout to Show in Tabular Form.