Excel how to sum all cells with the same name

Question

I have a workbook with 30 or more sheets. Specifically, each sheet may be an invoice, change order, etc. such that the format of each sheet is not the same as the next one.

The named range Total on each sheet may refer to one or more random cells on that sheet. On the last sheet, I want one cell to show the sum of all cells named Total.

The number of sheets may vary. I do not want to use a pivot table.

Is this possible?

Addendum: The formula below will return the total of all cells that belong to any name "Total" on any of the listed sheets, but I would like to automate it so I don't have to manually add all of the sheet names. The name Total may refer to one cell or any collection of cells on each sheet. In actual use, the sheet names could be any random name. So, I'm looking for code that will look at each sheet, and if the sheet contains the name Total then it appends +SUM(sheetname!(Total) to the equation. The SUM function is necessary so that if there are two or more cells per sheet in the named range Total, then they are included in the GrandTotal. =SUM(Sheet1!Total)+SUM(Sheet2!Total)+SUM(Sheet3!Total)+SUM(Sheet4!Total)

Another example: The following shows why I think it's possible to do what I want in a single step. Formulas=GET.CELL(48,INDIRECT("rc",0)) creates a real-time dynamic named range "Formulas" that includes every cell in the workbook which contains a formula. You can then conditionally format all cells to have a shaded background when the cell contains a formula.

So, I'm looking for a function something like: GrandTotal=SUM(GET.CELL("Total",INDERECT("rc",0)) where Total efers to any cell that belongs to a range named Total.

Answer 1

You can write in the summary sheet 2 columns for each invoice write the name of the sheet

Invoice Total
Sheet1

Sheet2

Sheet7

if sheet1 is in A2, in the second column :

=INDIRECT(A2&"!"&"total") you will have the value of total invoice then you can add it for Grand Total

Answer 2

Old question, but maybe it helps someone with the same problem. I had the same issue and wrote a function for it:

Public Function SumNamedRanges(nm As String) As Double
    Dim sh As Worksheet
    Dim test As Range
    For Each sh In Sheets
        '  check if the named range exists
        On Error Resume Next
        Set test = sh.Range(nm)
        If Err.Number = 0 Then
            On Error GoTo 0
            '  sum the ranges
            SumNamedRanges = SumNamedRanges + _
            Application.WorksheetFunction.Sum(test)
        Else
            On Error GoTo 0
        End If
    Next
End Function

The function does not recalculate itself, though