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