Dynamic Sum Across Sheets

[davin1130]

My workbook contain 13 sheets. One sheet for each month (January thru December) plus one sheet to be ale to run totals. I can easily use =Sum(January:December!B2) to calculate the sum of all B2 cells in sheets January thru December. However, i want the ability to select the range of sheets based off of cell A1 (which will have the desired beginning month) and cell A2 (which will have the desired ending month). Please Help !

 

[Jeffrey Mahoney]

Will the range of sheets selected be in chronological order? For Example:
Jan-Mar
Sep-Dec
Jun-Dec

but not
Jan, Jun, Dec
May, Jul
Aug, Sep, Dec

 

[davin1130]

you are correct ... Jan-Mar, Sept-Dec, Jun-Dec

 

[Jeffrey Mahoney]

I have tried every which way to Sunday to create a normal formula to express the sum of all the values across a 3d Range. There seems to be some folks that created 3d dynamic ranges, but I couldn't get that to work. INDIRECT won't work with 3D ranges. So I created a User Defined Function (UDF) that can be used in a cell formula. Paste that into a standard VBA module (ie, module1, module2). In a cell type in this formula:
=SumRange3D(B4)
* Where B4 is the range in all the months that you want to sum.
* Copy down and across as needed.
* UDF's should be used sparingly because they tend to take longer to calculate then standard Excel Functions. A few hundred won't make that much of a difference

'Provide current cell to Rng
Function SumRange3D(Rng As Range) As Double
  Dim BegMonth As Integer
  Dim EndMonth As Integer
  Dim X As Long
  Dim MonthName As String
  Dim Tot As Double
  Dim RngAddr As String
  
  Application.Volatile
  
  BegMonth = Range("A1").Value      'First month as a number
  EndMonth = Range("B1").Value      'Last Month as a number
  RngAddr = Rng.Address             'Address of current cell
  
  For X = BegMonth To EndMonth
    MonthName = Format(DateSerial(2019, X, 1), "mmmm") 'Full Month Name
    Tot = Tot + Sheets(MonthName).Range(RngAddr).Value  'Add to total
  Next X
  SumRange3D = Tot
    
End Function

 

[Jeffrey Mahoney]

I forgot to tell you that cell A1 needs to be a number expressing the first month in the series and Cell B1 needs to be the last month. You could change the ranges for these and change the macro to look at the new addresses.

 

[James006]

Hello,

As an alternative to the UDF, in order to have a Dynamic 3D Sum of selected Sheets, you can use an Array Formula ...

1. Create a Named range : Sheets in order not to list all the Sheets names ...
in the Refers To =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

2. Then use cell B3 to type in your ' From Sheet ' --- in cell B4 type in your ' To Sheet ' and in cell B5 your Target Cell or range

3. The following Array Formula will generate the Sum you are looking for

=SUM(IF((MATCH(Sheets,Sheets,0)>=MATCH($B$3,Sheets,0))*(MATCH(Sheets,Sheets,0)<=MATCH($B$4,Sheets,0)),N(INDIRECT("'"&Sheets&"'!"&$B$5))))

Hope this will help

 

[Jeffrey Mahoney]

James,

What exactly is the named range formula returning? Is it just returning sheets? I have Sheets January through December and they are Worksheets 19 through 30 respectively. Do I need to change the GET.Workbook(1) to Get.Workbook(19) and Get.Workbook(16) to Get.Workbook(30)?

Or

Get.Workbook(1) does not return a sheet name.

OR

Get.Workbook(1) returns the sheet names with the workbook name and SUBSTITUTE removes the workbook name

Jeff

 

[Jeffrey Mahoney]

Still trying to help out the OP by evaluating the formulas given by James

I got the Named range created and the formula below works.

I replaced the end part of the formula to include a cell reference rather than a single cell. If one were trying to use this to get the sum of many rows and columns from the 3D range, we would have one issue.

Let's say that the Month tables all have a range of B4:J100. And I want my Totals to also show up in the same range on the Summary sheet. When I try to use the CELL("address",B4) instead of a hard coded B4, it complains that there is circular reference. Strange, because I'm not creating my totals on any of the month pages. The Sheets named range must be confusing the formula to think that all sheets are being evaluated.

To solve this, we would have to put the Totals formulas in different columns then values on the Month Sheets. The formula below could be in M4; it would return the sum of all the Month values in cell B4.

=SUM(IF((MATCH(Sheets,Sheets,0)>=MATCH($A$3,Sheets,0))*(MATCH(Sheets,Sheets,0)<=MATCH($A$4,Sheets,0)),N(INDIRECT("'"&Sheets&"'!"&CELL("address",B4)))))

on my summary sheet, I put the beginning month in cell A3 and the End Month in cell A4.

Jeff

 

[James006]

Hello Jeff,

Should you need a reference link for the Old XL4 macro language ...( and the 38 Type_nums used by Get.Workbook ) ... see below :

https://answers.microsoft.com/en-us...cel-2011/a359708d-b5b7-48ba-8d1e-c4114d4642d2

Hope this will help

 

[davin1130]

THANK YOU FOR YOU HELP THUS FAR !!!!
I'm still running into problems though ... i created a named range and named it "Sheets", putting the formula =Substitute(get.workbook(1),"["&get.workbook(16)&"]","") into the "Refers to" field AS IS ... the cell i'm using to put the formula in is Q8 and i modified the formula changing $A$3 to $A$1, $A$4 to $A$2, and B4 to B2 ... Cell B2 in all sheets has a value of 1 so i'd expect my totals value to be 3 since i'm looking at sheets January/February/March .... i'm getting a value of zero using both formulas. Any ideas what i'm doing wrong ??