Jtdylktuy

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.

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

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