Formula auto update

Ok you will have first extract all sheets which are in your workbook

There is and old Macro function GET.CELL which works in all excel version for extracting various kind of information. I have use one of them formula for extracting sheet name. I have created a name manger to do so follow the steps.

Press Alt->M->N -> click on New -> put name SheetList and in refer to =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1))," ") and then ok.

in Monthly sheet

U3

Formula:

=IFERROR(TRIM(INDEX(SheetList,ROW()-2)),"")

Copy down.

We need another name manger with name SheetRange and put refer to =OFFSET(MONTHLY!$U$3,,,COUNTIFS(MONTHLY!$U$3:$U$41,"?*")-3)

Now

Monthly Sheet

F6

Formula:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetRange&"'!"&CHAR(COLUMN(BR:BR))&":"&CHAR(COLUMN(BR:BR))),INDIRECT("'"&SheetRange&"'!B:B"),$B6))

Copy across.

Check the attachment,