Summary data

[Tamara]

I have a workbook that contains several different pages that each Producer is
supposed to update. On the summary page, I need to have the option to choose
what to display and how it is displayed.

For instance, Jeff's prospect list has the following:
Company Contact Revenue Potential Sales Potential # of EE
ABC Co. Joe Smith $1,000 75% 12

Joe's prospect list has the following:
Company Contact Revenue Potential Sales Potential # of EE
123 Co. Jane Smith $3,000 25% 2

The summary page needs to list the following:

Producer Co. Contact Rev Pot Sales Pot # of EE
Jeff ABC Co. Joe Smith $1,000 75% 12
Joe 123 Co. Jane Smith $3,000 25%
2

I've tried to link the cells and it just doesn't work like I want. Any
suggestions?

I cut down on column headings for space reasons, actual column headings are:
Effective Date, Client Name, Next Step, Revenue Potential, Sales Potential
%, Contact, # Lives, Current Broker.

Column headings on summary page is the same except the addition of
"Producer" in order to be able to easily identify who has what.

Thoughts?

 

[Jim Thomlinson]

Generally speaking it is normally better to put all of the source data on one
sheet and then use filtering, subtotaling and/or pivot tables to produce
reports. That being said Ron's site has some examples of how to merge
multiple sheets into a single summary sheet. Note that to effectively create
a summary all of the solutions will involve macros...

http://www.rondebruin.nl/tips.htm

 

[Tamara]

Hello Jim,

Thank you for responding. Each producer has their own prospect list that
they don't want everyone to see. However, I need to report back expected
revenue for ALL the producers and be able to show a sort of 'pipeline' of
what is about to come in the door. My only option really is a summary sheet.
I tried using a pivot table but got confused with 11 worksheets feeding into
it and it kind of messed up.

Thank you for trying to help.