Auto Update Formula

[KevinHolden]

So, I have a workbook containing multiple worksheets and I'm trying to clean it up a little bit. This workbook is a list of people that my company contacts and it states if they accept an invitation from us when we contact them or not (in other words there are a bunch of yes and no's next to company names). We have to attempt to contact these same people for every project that we have.
In the past we just made a new workbook, put the companies that we know on there (along with their contact information), and gave them a call. I have made one general workbook for all the projects with the first worksheet being somewhat of a template. Every time we have a new project I open a new worksheet on the general workbook, copy the names over from the template, and give the people a call. So now you can see all of the projects under one worksheet.

Now here is what I want to do:
1) count all of the accepts and declines from individual companies on each worksheet but only show the count on the template worksheet. This way people can open the workbook, look at the first "template" worksheet, and see who our most consistent/inconsistent companies are. I assume you would need a COUNTIF formula, but how do you get it to count the yes's and no's in each worksheet without having to make an incredibly long formula that you add onto each time you make a new worksheet? Is there a way to make it automatically update the formula?

This is what I'm currently looking at =COUNTIF(Sheet2!$E5,"yes")+COUNTIF(Sheet3!$E5,"yes")+... and =COUNTIF(Sheet2!$E5,"no")+...

2) Also, I don't know if this is possible, but if a coworker happens to find a new company and lists it in one of the sheets that isn't the template, can I get the template to auto-populate? In layman's terms, I want it to notice if someone listed a new contact and at least put the company name in the template. That way I can see the name and update it later. Would this require a VLOOKUP? I know this is probably asking too much but I figured while I'm already asking the other question I might as well shoot this one out there.

Thanks for any help!

 

[starl]

For your first issue, see if one of the suggestions here will help.

For #2.. you would need code. Maybe a Change_Event on the column(s) the company name is added to.

Are you the only person who makes the new sheets? Normally, I would not design a workbook where the template is also a report. Templates are usually hidden so users don't have to worry abou them. I would have the report be a separate sheet.

Also - when creating a new sheet, why not just copy the template sheet itself? Why insert a new sheet then copy/paste from the template?

 

[KevinHolden]

I didn't use the template sheet itself mainly because I was using it as a template and report. I am the only one who uses this template and creates new sheets as of now so that's why I haven't worried about making separate ones for each. Though I'm sure I will eventually come across this problem so that is probably something I should look into. I'll let you know if those other suggestions work though.
Thanks!