Posts by Bullmacher1

    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?

    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