1

I'm trying to create an 3d excel formula with a dynamic reference to the bookmark sheets (start and end of range). The end goal is to have a standardised calculation sheet that can be used to consolidate different groupings (each of which will most likely have numerous sheets) based upon a provided sheet specific input.

I have contemplated the use of a CHOOSE function but this feels less dynamic and bulkier. I have also contemplated using an INDIRECT function (and just generating specific sheet names instead of bookmarks) but would rather avoid the volatility and need for identifying individual sheets if possible.

I have attached a simplified screenshot of what I am trying to achieve.

Example setup

The formula that I am trying to use is as follows:

=SUM("'"&$E$4&">:<"&$E$4&"'!A1")

When I F9 (using the variables from the screenshot) this I get the following:

=SUM("'A>:<A'!A1")

Whereas the statically typed formula that I am trying to replicate is this:

=SUM('A>:<A'!A1)
JMP
  • 4,417
  • 17
  • 30
  • 41
Kieran
  • 23
  • 2
  • You can dynamically refer to worksheets with named range .. [refer this](https://stackoverflow.com/a/61545265/9808063) .. and .. [this link](https://stackoverflow.com/a/60893874/9808063) – Naresh Jul 13 '21 at 10:29
  • Hi Naresh, cheers for the GET.WORKBOOK suggestion. This is something that I was actually playing around with earlier as well and have since incorporated into my solution (based upon JMPs response). – Kieran Jul 13 '21 at 12:49

2 Answers2

1

If your bookmarks are already sequential, and the sheets inside them have a consistent naming schema, then you can find the location of the bookmarks with

=SHEET("a>")

and

=SHEET("<a")

Then you can use INDIRECT and SEQUENCE to run through each sheet inbetween.

=SUM(INDIRECT("Sheet"&(A3+SEQUENCE(A4-A3-1))&"!A1"))

In the example, A3 and A4 hold the sheet number of the bookmarks, and the sheets are eponymous, Sheet3, Sheet4, etc..., and we sum the A1 cell in each sheet.

bookmarks

JMP
  • 4,417
  • 17
  • 30
  • 41
  • Unfortunately the sheets inside don't necessarily have a consistent naming schema but I have been able to tweak the example that you provided to produce the desired outcome. For reference this is the approach that I have taken: =SUM(INDIRECT(INDEX(sheets,A3+SEQUENCE(A4-A3-1))&"!A1")). Where "sheets" is a named range with the following formula: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())#. Thank you very much for your contribution, it's much appreciated! – Kieran Jul 13 '21 at 12:43
0

You can't use a SUM across multiple worksheets: the entirety of each range has to be within a single worksheet. SUM('A>:<A'!A1) is an invalid formula — you would need to use SUM('A>'!A1,'1'!A1,'2'!A1,'<A'!A1) to get the result you want. (i.e. specifying multiple ranges)

INDIRECT and CHOOSE will ignore things like Sheet Order, so can't be used for this either.

You might be able to work something out with VBA, using Worksheet.Index references, but not with normal Excel formulae. Here is a User Defined Function (put it in a new Module in VBA, not in the Worksheet!) that should do what you are after:

Option Explicit
Option Private Module

Public Function SUM3D(reference1 As Range, reference2 As Range) As Variant
    Application.Volatile
    
    SUM3D = CVErr(xlErrNA) 'Default to an Error
    On Error GoTo FunctionError
    
    Dim SumRange As String, TotalSum As Double, TempSum As Variant, StartIndex As Long, EndIndex As Long, CurrIndex As Long
    TotalSum = 0
    TempSum = 0
    
    'Create the 2D range bounded by the 2 references, which will extend through the sheets
    SumRange = Worksheets(1).Range(Worksheets(1).Range(reference1.Address(True, True, xlA1, False)), _
        Worksheets(1).Range(reference2.Address(True, True, xlA1, False)) _
        ).Address(True, True, xlA1, False)
    
    StartIndex = reference1.Worksheet.Index
    EndIndex = reference2.Worksheet.Index
    
    
    If StartIndex = EndIndex Then
        'Same sheet, no need to make it 3D
        SUM3D = Application.Sum(reference1, reference2)
    Else
        'Different sheets, so cycle through them.
        For CurrIndex = StartIndex To EndIndex Step Sgn(EndIndex - StartIndex)
            'Ignore Charts, DialogSheets, MacroSheets, etc
            If Sheets(CurrIndex).Type = xlWorksheet Then
                TempSum = Application.Sum(Sheets(CurrIndex).Range(SumRange))
                
                If IsError(TempSum) Then
                    SUM3D = TempSum 'Return the Error
                    Exit Function
                Else
                    TotalSum = TotalSum + TempSum 'Add this sheet's sum to the total
                    TempSum = 0
                End If
            End If
        Next CurrIndex
    End If
    
    SUM3D = TotalSum
FunctionError:
    On Error GoTo -1 'Clear error handler
End Function
Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • With normal excel we can refer to worksheets with index dynamically with named range .. [refer this](https://stackoverflow.com/a/61545265/9808063) .. and .. [this link](https://stackoverflow.com/a/60893874/9808063) .. but with the risk of rearranging the sheets order :) I guess – Naresh Jul 13 '21 at 10:37
  • 1
    @Naresh Hmm… I wonder if an ArrayFormula/`SUMPRODUCT` could be set up to work on "every sheet between these two indices" then? – Chronocidal Jul 13 '21 at 10:56
  • Apologies, I may not be getting you correctly. If the cell is the same on each sheet like in this case, A1, I think it could be done with that kind of named range. If the cells are different on different sheets, then no. – Naresh Jul 13 '21 at 11:06
  • Hi Chronocidal, I have actually managed to resolve my issue with a modified version of JMPs suggestion but thanks for your input as well, it's much appreciated. However, I can confirm that "SUM('A>: – Kieran Jul 13 '21 at 12:42