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!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Sum across worksheets formula not updating
Jonmo1
MrExcel MVP
- Joined
- Oct 12, 2006
- Messages
- 44,061
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
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
Upvote
0
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!
Upvote
0
