How to 3D sum across sheets when cell locations are different ?

[scott14]

Say 159 worksheets each have a cell named 'Hours' but it's in a different location on each sheet.
How can you 3D sum across the 159 sheets for grand total Hours? In other words, instead of summing the same fixed cell location on each sheet, you sum the named cell which has different locations on each of the sheets.

It doesn't work to sum on the name: =SUM('Jan 2008:Apr 2021'!"Hours")
Thank you.

 

[offthelip]

The way I would do that is to write a bit of vba to write the equation for you, This code should do it ofr you. I have assumed that you want to add together the cells which are just below the "Hours"
I have put hte equation in A1 on sheet1, change it to where you want it:

Sub atest()
Dim rr As Range
form = "="
For i = 1 To Worksheets.Count
 With Worksheets(i).UsedRange
    Set rr = .Find("Hours", LookIn:=xlValues).Offset(1, 0)
     rradd = rr.Address
     form = form & Worksheets(i).Name & "!" & rradd & "+"
    
 End With
Next i
' remove the last + sign
form = Left(form, Len(form) - 1)
Worksheets("sheet1").Select
Cells(1, 1).Formula = form

End Sub

 

[Domenic]

Try...

=SUMPRODUCT(N(INDIRECT("'"&TEXT(DATE(2008,ROW(INDIRECT("1:160")),1),"mmm yyyy")&"'!Hours")))

Hope this helps!

 

[scott14]

offthelip & Domenic​

Thanks to each of you for replying! Frankly, I'm not advanced enough to truly understand either of your solutions. I plan to put time into figuring out how to make your solutions work - it must be something I need to learn about, and I will slowly over time.
When offthelip's vba is inserted, saved and run, an error message says: Run time error '91': Object Variable or With Blocks variable not set.
When Domenic's incredibly complex formula is put into the summary sheet, that cell shows #REF!.

Thanks to you both for your helping hand! I really appreciate it. Scott

 

[Domenic]

You'll get #REF! when one or more worksheets within the range Jan 2008 and Apr 2021 doesn't exist. To allow one or more worksheets to be omitted, try the following instead...

=SUMPRODUCT(IFERROR(N(INDIRECT("'"&TEXT(DATE(2008,ROW(INDIRECT("1:160")),1),"mmm yyyy")&"'!Hours")),0))

 

[scott14]

Domenic,

Thank you. Your revised formula is very encouraging because now instead of #REF!, it gives the number 0. So it's almost working.

I've experimented with various tweaks to your revised formula, but Excel 2010 doesn't like any of my tweaks so far. Again, sincere thanks to you for your time and suggested formula, which you obviously put a lot of effort and thought into.

 

[offthelip]

You will get the run time error if "hours" is not found on one sheet. To get over this I have added error checking. I have notice from Domenic input that I might have misunderstood the requirements. i.e. if "Hours" is a worksheet named range the code needs to be slightly different. so I have included a subroutine to do that

Sub atest()
' use this is "Hours" is text title in a cell above the number
Dim rr As Range
form = "="
For i = 1 To Worksheets.Count
 With Worksheets(i).UsedRange
 On Error GoTo Skip:
     Set rr = .Find("Hours", LookIn:=xlValues).Offset(1, 0)
     rradd = rr.Address
     form = form & Worksheets(i).Name & "!" & rradd & "+"
Skip:
 End With
Next i
' remove the last + sign
form = Left(form, Len(form) - 1)
Worksheets("sheet1").Select
Cells(1, 1).Formula = form

End Sub

Sub atest1()
' use this if "Hours" is a workhseet named range
Dim rr As Range
form = "="
For i = 1 To Worksheets.Count
 On Error GoTo Skip:
       Set rr = Worksheets(i).Range("Hours")
     rradd = rr.Address
     form = form & Worksheets(i).Name & "!" & rradd & "+"
Skip:
Next i
' remove the last + sign
form = Left(form, Len(form) - 1)
Worksheets("sheet1").Select
Cells(1, 1).Formula = form

End Sub

 

[Domenic]

My understanding is that your worksheets are named Jan 2008, Feb 2008, Mar 2008, and so on, all the way to Apr 2021, correct? And that there's a space between the month and the year, correct? And that each worksheet contains a cell named Hours, where the name has been defined using either the Name Box or Name Manager, correct?

 

[scott14]

Domenic​

Yes, all that you've said is correct. Each worksheet name is like Jul 2012 Aug 2012 Sep 2012, ...etc.
Each worksheet has one cell for which the name Hours is defined for tje scope of just that sheet only. The named cell Hours is appears omly once on each sheet.

 

[Domenic]

Make sure that the cells that contain your hours contain numbers that are recognized as numerical values, not text values. You can use the ISNUMBER function to test for numerical values. So, for example, the following formula returns TRUE if the value is a numerical value. Otherwise, it returns FALSE...

=ISNUMBER('Jan 2008'!Hours)

Do those cells in fact contain numerical values?