Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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
@AlexP. I can think of too many reasons to fit into a comment, but one is cost! – pnuts Apr 2 '13 at 19:33

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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