VBA for data consolidation with ability to exclude some data.


New Member
May 21, 2020
Office Version
  1. 2016
  2. 2013
  1. Windows
  2. MacOS
Hi all, first post but long time browser.

I've got a workbook with 10 tabs which relate to a persons time spent on a task and a summary sheet with a consolidated view of the data.

So for example, each of the 10 tabs is names with the individual person and set up with:
  • A list of Projects in cells A2:A11
  • Months in the cells B1:M1
  • Data in the cells in number format to 2dp
  • a sum at the bottom of each column.
The summary tab is currently set up with the same template and in for example cell B2 which would be the equivalent of Project A & January I have the formula:

=SUM('nameone'!B2,'nametwo'!B2,'name three'!B2, etc..

This gives me the total of all hours and works because the worksheets are set int he same template.

However, what I now need to do is figure out a method to have the ability to exclude certain individuals from the summary. Originally I explored the option of having another tab with each name and a 'Yes' or 'No' option and then using a rather long winded =SUM(IF(nameone="No","","'nameone'!B2",IF( etc.. formula which looks very messy and complicated.

Now I am trying to figure out how to create a user form which will allow me to tick and untick individual names which will then include/exclude them from my summary template with the help of some sort of refresh button. I have limited ability when it comes to VBA - I've worked through an online tutorial before and used this site for tips on what I needed to know for simpler macros but as always keen to learn new ways of working.

It has also crossed my mind that this may be possible with the use of a pivot but I may need some advice on how to use ranges from multiple sheets as its not something I've done before.

Thank you all in advance.

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Latest member