INDIRECT Sum across multiple sheets alternative

  • I have a rather large/complex workbook. Within it there are data entry sheets and aggregation sheets, they all have the same format, but the aggregation sheets pull in the equivalent cell from it's "children". e.g data would be entered into 'Brazil', 'Chile', 'Mexico', and the LATAM cell E5 would sum E5 from those tabs.


    I'm currently accomplishing this using a formula of the form:


    SUMPRODUCT(SUM(OFFSET(INDIRECT("'"&SheetList&"'!A1"),ROW(E5)-1,COLUMN(E5)-1)))


    SheetList is a dynamic range defined on each summary sheet with the list of children to be summed. (Brazil, Chile, Mexico in this example)


    I'd like to convert this to a non-volatile function as the current calculations are frequently erroring out and slowing down the performance. One thought I'd come across is to organize all the tab hierarchy in a table, but I am unsure of how to reference the sheet names in the table without using indirect.


    Thanks for your help

  • Re: INDIRECT Sum across multiple sheets alternative


    Here is an example of something you could do with code.


    This would total A1 in the sheets listed, but only when the button is pressed.


    You can add or subtract names from the list as long as there are no blanks in the list.


    Here's the code and below that is a working file.


    Don't worry if you don't understand the code it's just there for anyone to look at and learn from or perhaps get some ideas.


    Code
    Sub SumSheets()
    Dim r As Range, num As Double
    With Worksheets("Sheet1")
        For Each r In .Range(.Range("A2"), .Range("A1").End(xlDown))
            num = num + Worksheets(r.Value).Range("A1").Value
        Next r
        .Range("C2").Value = num
    End With
  • Re: INDIRECT Sum across multiple sheets alternative


    Thanks for the reply. A UDF of this kind (rather than a Sub) could possibly work. Could "A1" be changed to either a user defined cell or the cell in which the function is placed?


    Also, what kind of performance would a UDF of this kind have relative to what currently exists? It would also be volatile, correct?

  • Re: INDIRECT Sum across multiple sheets alternative


    I can't say about performance you'd have to test it out.


    I went with the button because it would only calculate when pressed.


    There are certain events, for instance the worksheet change event that can also be used to trigger the code.


    The worksheet change event is only triggered by someone manually changing a value in a cell or making a selection from a drop down, it's not triggered by the value in a cell being changed as the result of a formula calculating.


    If I changed a certain cell on a certain worksheet the code could be triggered.


    With the change event you have the luxury of checking if the cell that changed was in a certain range and it it wasn't the code can be bypassed thereby you don't have to worry about the calculation taking place possibly slowing down your workbook.


    The code could be triggered every time a certain sheet is activated, deactivated, when the workbook is first opened, there are quite a few possibilities.


    There's also the selection change event which is triggered when you change what cell you have selected on a sheet.


    So for instance in the sample I sent you I could check if the cell I just selected on Sheet1 is C2 and if so the code would run, so select C2 and the cell gets updated, but only when you select C2.


    As far as a UDF, you can do what you want, you could have it so you type in a cell address and have it draw data from that cell on the sheets in the list.

  • Re: INDIRECT Sum across multiple sheets alternative


    So I tested a custom function where you would enter the cells with the sheet names and then a cell address for the cell to check on those sheets and it only runs when I either click into the formula and hit enter or I change a name in the list or I force recalculation of the whole sheet.


    I named it CustomAdd and to use it with the sheet I sent you it would look like what you see below, with A2:A4 being the list of cells with the sheet names and A1 being the cell to check on each sheet.

    Code
    =CustomAdd(A2:A4,A1)


    Code
    Function CustomAdd(SheetList As Range, Cell As Range) As Double
    Dim r As Range
        For Each r In SheetList
            CustomAdd = Worksheets(r.Value).Range(Cell.Address).Value + CustomAdd
        Next r
    End Function

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!