-2

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.

  • Please note that Super User is not a script writing service. If you tell us what you have tried so far (including any scripts you are using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?. – DavidPostill Mar 25 '16 at 23:26
  • In the first place, how did you give the same name to ranges in different sheet? I'm on Excel 2016 and I'm unable to replicate your situation – Prasanna Mar 26 '16 at 1:21
  • Prasanna - Excel has two levels of named ranges, workbook level, and worksheet level. Here's one way to change it: Start with a single blank sheet. Name your ranges; these will most likely be workbook level ranges by default. Now right-click the sheet tab and copy to a new sheet. The new sheet will have worksheet level ranges. Delete the original sheet. If you continue to make copies of the sheet they will be worksheet level named ranges. – Dickie Dunkin Mar 27 '16 at 14:08
  • Regarding your example with the Formulas named range to shade cells with a formula: The formula you've given does not work as a named range on which you can perform functions. It returns either TRUE or FALSE depending on whether or not a particular cell has a formula in it. If you use it for conditional formatting and apply it to a large range, it will evaluate each cell individually. – Engineer Toast Mar 28 '16 at 20:45
  • To learn more about Get.Cell, take a look here – Engineer Toast Mar 28 '16 at 20:46
0

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

0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.