Consolidating worksheets

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
210
Hi,

I have a number of worksheets that are all identical in terms of format, structure, etc. They are templates and have numbers in them. I have created a summer sheet so I can sum all the numbers in to totals. This summary shee is simply one of the templates but blank.

I there a easier way to sum the values instead of physicaly adding and linking the cells? I know doing it this way won' take long as I can simply drag the formula down and across. But I remember that I came across a more sophisticated way that also didn't involve running a macro in VBA.

Any assistance would be gratefully recieed.

Regards

Ben
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,154
Office Version
365
Platform
Windows
Hi Ben,
depending on your structure this might work:
=SUM(Apr:Jun!B6) -> this should sum up all cells B6 in the sheets Apr, ???, Jun
This formula assumes:
-the first sheet is named "Apr", the last "Jun"
-the number of sheets in between is not fixed (so one sheet named "May" is logical, but you can have multiple)
-it would add up all the cells B6 in those sheets
-if your sheet names have spaces or are numbers, you might need [] or '' around the sheet names, e.g. =SUM(‘(9):(27)’!D7)

Hope that helps,
Koen
 

Forum statistics

Threads
1,082,316
Messages
5,364,506
Members
400,804
Latest member
davileal

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...
Top