Formula auto update

Hello every one,

I need help of some excel experts, I have an inventory sheet, and I daily update sumif Formula in Monthly sheet. I want to d something special,😄. I want, when i add new sheet as next date, then formula should auto update in monthly sheet, If you have some ideas, So please share with me.

Thanks,

Best Regard: M. Shahbaz Anwar

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,

Thanks for great effort<
But i think you could not understand my real problem, "I want, If I add new sheet in this workbook then formula should auto update in monthly sheet.
I have tried in your attached file, As i add new sheet in this workbok as name "05 DEC", then formula become disturbed..... Are you have any solution please.....
I am sending you some screen shots.....

Please check screen shot I hope you will understand.

I want to "Auto update monthly sumif formula, as i add new sheet, then formula should auto update as new sheet added"

Thanks if you understand...

ANY ONE PLEASE HELP,,,SOLVE THIS PROBLEM////

You must refresh in the master sheet column U. Select U3:U10 then F2 and Control+Enter

Sorry sir,
All in vain, Not working this formula, Any other or its relevant formula,,,
I want to monthly sheet auto sum as i add new sheet in the workbook.

Thanks for interest in my post,
Sorry for weak English,
Sir, I think you also did not understand, what my problem.
I have already placed sumif Formula in Monthly sheet for Monthly Report. Now i want a formula that auto update that sumif formula in monthly sheet,
as i have used sum if formula in column F to L.
As now i have created 4 sheets before monthly sheet, And in Monthly sheet formula is =SUMIF('02 DEC'!$B:$B,MONTHLY!$B6,'02 DEC'!F:F)+SUMIF('03 DEC'!$B:$B,MONTHLY!$B6,'03 DEC'!F:F)+SUMIF('04 DEC'!$B:$B,MONTHLY!$B6,'04 DEC'!F:F)+SUMIF('05 DEC'!$B:$B,MONTHLY!$B6,'05 DEC'!F:F)

I want auto update above formula If I enter new sheet like ''06 DEC''. Then formula should auto update by the name of sheet "06 Dec" in Monthly sheet cells E5 to L403.
AS adding new sheet "06 DEC"
=SUMIF('02 DEC'!$B:$B,MONTHLY!$B6,'02 DEC'!F:F)+SUMIF('03 DEC'!$B:$B,MONTHLY!$B6,'03 DEC'!F:F)+SUMIF('04 DEC'!$B:$B,MONTHLY!$B6,'04 DEC'!F:F)+SUMIF('05 DEC'!$B:$B,MONTHLY!$B6,'05 DEC'!F:F)+SUMIF('06 DEC'!$B:$B,MONTHLY!$B6,'06 DEC'!F:F)


Is this is possible?
I think this is possible......
Or any relevant formula, which sum all previous sheet's specific value of cells..
Thanks,,,
And Ignore if you don't understand,,

Copy paste U column formula again when you add any sheet.

Thanks sir, I understand now
But 1 question more!
Is this possible, "Wheni add 06 DEC or new sheet before MONTHLY sheet, then the name of sheet (06 DEC) automatically add in column U under sheetrange cells.
Hope you understand.

Alternative ...

Private Sub Worksheet_Activate()


Application.ScreenUpdating = False

Dim i As Integer
Dim ws As Worksheet
Dim rng As Range
Dim ShtArr() As Variant
ReDim ShtArr(1 To 31, 1 To 1)
r = 0
With ActiveSheet

For i = 1 To Worksheets.Count
If IsNumeric(Left(Worksheets(i).Name, 2)) Then
r = r + 1
ShtArr(r, 1) = Format(Worksheets(i).Name, "dd mmm")
End If
Next i

Set rng = .Range(.Cells(3, "U"), .Cells(33, "U"))
rng = ShtArr

End With

Application.ScreenUpdating = True

End Sub


Click om monthly tab==>View Code==> copy/paste above

Assumes sheets to be included are of form "dd mmm" and maximum of one month (31 days)

Thanks for your help,,
My problem is solved by your kind effort..
Again thanks///