Need formula to add cell B5 on all available sheets (future sheets will be created) and display the total on cell D5

0
votes

I need a formula on sheet1!D5 to sum cell B5 from all available sheets. Problem is that i have a command button on Sheet1 that creates a new sheet and gives it a name based on the current month and year. So, i can't just put =SUM(sheet1:sheet5(B5)) because any new sheet will be created outside of sheets 1-5 and not be included into this formula.

I have tried =SUM(sheet1:sheet5(B5)), but that will only work for a while.

I need the formula to add B5 from any sheet, including any newly created sheets. Im open to using VBA, but i figured it may be easier if there's a formula i could just stick into a cell and have it copied to every new sheet that's created.

The code suggested below works fine, but for some reason the first code doesn't update itself. I have to click on D5 and press enter everytime (which isn't what i want) D5 Doesn't Update Itself

1
excelexcel-formula

1 Answers

2
votes

Perhaps a UDF:

Function AddB5() As Double
    Dim ws As Worksheet
    Dim B5Return as Double

    For Each ws In ThisWorkbook.Worksheets
        B5Return = B5Return + ws.Range("B5").Value
    Next

    AddB5 = B5Return
End Function

Stick that in a new module then you can use it in a formula:

=AddB5()