Auto Update Formula


New Member
Aug 14, 2014
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!


Aug 16, 2002
Office Version
365, 2019
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?


New Member
Aug 14, 2014
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.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...