Sum across worksheets formula not updating

[Websterja]

I have the formula to sum a cell across worksheets, but I cannot seem to get it to automatically update when I add another worksheet. For now, I have sheets labeled for each day I work, such as Nov 1, Nov 2, etc. I have the formula now as =SUM(‘Nov 1:Nov 7’!L1). When I add a worksheet for tomorrow, I will insert it before the totals sheet and label it Nov 8. When I do that, the formula still ranges only to Nov 7. Can I get it to update or will I have to resort to manually changing it each time? Or simply create the totals at the end of the month? Thanks guys!

 

[Jonmo1]

If you want a new sheet to be included in that sum, you need to insert it 'Between' the first and last sheet shown in the formula
i.e. To the right of Nov 1, and To the left of Nov 7

I suggest making blank dummy sheets on either side of the first and last sheet you want included.
To the left of Nov 1 = LeftDummy (name it whatever you want)
To the right of Nov 7 = RightDummy

Then use
=SUM('LeftDummy:RightDummy'!L1)

Now insert new sheet between Nov 7 and DummyRight

 

[Websterja]

Ok I see what you’re saying. In this case I placed the first and last day of the month, then I can add days between those. That seems to work. I was trying to add between the last day and the totals sheets, I’m guessing that’s why it didn’t update. I appreciate the assist!

 

[Jonmo1]

You're welcome.