KevinHolden

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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,740
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top