Best formual or a pivot table?

[Lynda Ritchie]

Hi,

I am wondering if anyone could help me with which formula, or if a pivot table or link workbook would be the best. I have a table with debtors, some details and the staff who look after the customer. I want to show on seperate tabs, instead of filtering, who is looking after what account so I can see where they are up to. This is only an excerpt the worksheet has 900 customers. I want each sheet e.g. Mary (see attachment) to show what she is working on only. I feel vlookup wld leave blank lines which I do not want.

Any help would be greatly appreciated.

Please see spreadsheet attached.

Kind Regards
Lynda

[benishiryo]

hi Lynda, welcome to the forum. i did up 2 solutions for you. see which do you prefer. 1 is the pivot table in the "Pivot" sheet. after doing up 1 single pivot like that, here's a trick with the Report Filter.
1. Click anywhere on the pivot
2. you should see an Options tab appearing in the Ribbon. click on that
3. there's another "Options" button on the left under "PivotTable Name:". click on the triangle pointing down.
4. click on Show Report Filter Pages
5. click OK & you'll have the different names on different sheets

another one i did is the formulas version. i did it for just Mary & Sue. for the formulas to be much shorter, could you put the full name of the Contact Officer in cell S1? you can then copy the whole thing into another worksheet & just change S1 to a different name. takes care of blanks

[Lynda Ritchie]

Thank you very much Benishiryo, I never would have come up with that formula, and the pivot table looks great. If you know of any book or site I could practice my Excel I would be very grateful. I did a Intermediate, then Advanced Excel course earlier in the year but it brushed over everything, any advice would be great as well.

Kind Regards
Lynda

[djapigo]

Hi Lynda,

Follow the very first thread of the Excel Formulas and Functions forum...
http://www.excelforum.com/excel-form...additions.html

There are a ton of sites here... keep learning... ask questions here... good luck...