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).

  • Excel is by far the best component of the MS Office suite... but why won't you (and other people with similar requests) just use MS Access instead? – Alex P. Mar 21 '13 at 20:58

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.