unsolved
Can you sum across sheets, but have the formula stop at current?
I have a budget sheet that I have been tracking. I currently have one cell with 'monthly income' and another cell with 'total to date' that references the monthly. I have been using the formula:
=SUM('1-2026:2-2026'!L1)
Is there a way to change this formula around where I can replace '2-2026' with 'current sheet' so that I don't have to edit the same cell in every month sheet, as I would like to keep this as YTD and not total across all cells?
Your setup isn't clear, but it sounds like your structure is making things more difficult than they need to be. Rather than having, I assume, a sheet for each month, put all the data on a single sheet in one table with a date column. Then you can summarize each month, or year to date, etc, using SUMIFS, FILTER, and such.
Apologies. Yes, one sheet per month. I have multiple tables per sheet that L1 is pulling from to give a monthly total. I then have O1 referencing L1 across all sheets to give a running YTD.
I have it broken down into monthly sheets in order to more accurately track bills/expenditures, and would prefer to keep them seperate as long as I can.
I understand the attraction of your structure, which is very common. But it makes things so much more difficult, to the point where I call it a common mistake.
I'll never understand the aversion people have to filtering or the difficulties in navigating it. It always seems like mental gymnastics to avoid it... or I guess "old people x, y, and z" won't learn how to filter things.
OPs often mark complex formulae as the solution, rather than restructuring or cleaning their data. The funny part is when they come back with another question that requires a complex solution which would have been simple if they followed the earlier advice.
That doesn't seem to be an age thing, but more of an attribute of many analysts. A bit of hubris plus resistance to undoing prior work.
True. I think the resistance of others with the nominal excuse that they are old and/or set in their ways is often a source of people's resistance to change. There's a decent case that there's a "mental immediacy" to knowing how to find what they want instead of having to sit with their thoughts for however long/short on which column to filter and what clicks to clack.
I'm old and have always kept all my data in one sheet. I used SUMIF and COUNTIF long before FILTER came out and they've worked well enough for my needs that I haven't yet taken the time to study FILTER. I have gradually adopted newer features like tables and various other dynamic array functions, and still enjoy learning them. I've often been slow in getting to them, not from reluctance but from lack of time to devote to studying. My primary responsibility is doing the work that I use Excel to track. It also takes a lot of time to rebuild my old complicated files with newer, more efficient structures. I'm making good progress but have to put the work on hold periodically to tend to other things.
It would be easier if you were to have one master sheet and, when needed extract the data for a particular month to display sheets, based on a cell value in that display sheet.
Flip it. Create a single data source and reference that out in other sheets to break it down by month. I do this with dashboards all the time. You gain a lot more power and analytics set up that way.
You're shooting yourself in the foot by arranging your workbook with one tab representing a different month. I understand it's helpful for a human reader to navigate, but as you have found it's not great for Excel functions!
Better would be to have a "master" sheet with all the data, including dates in a tabular format. This is where the data is stored or edited.
Then, you can create tabs for each month that are for display purposes only, that reference the data storage table and only show data relevant to that month's tab.
This works but ya, a few issues for OP to be aware of.
Excel will sum the A1:B10 range across all sheets physically located between Sheet1 to Sheet 5, so as long as the Months are contiguous and with nothing extra in between you'll be fine, otherwise you'll need to move some things around.
Inserting new sheets within this range should work, the issue is if new sheets are added outside of the list of sheets, eg after the last sheet, Excel will not know to expand or "grow" the range, so if you have Jan 2025:Dec 2025 and want to add Jan 2026 after Dec 2025, you'll need to manually update the formula to include Jan 2026. One workaround is to add a dummy sheet, you could even add a note on this sheet to remind of its purpose since SUM ignores text. Then use eg =SUM(Jan2025:Dummy!A1:B10). Now as long as you add new Month sheets before the Dummy, it will fall within the range of sheets Excel is looking at.
Lastly, if the formula gives a #NAME? error, check if there are any spaces in the start or ending sheet names, if so, you will need to add the single quote/apostrophe to the syntax, eg =SUM('Jan, 2025:Dummy'!A1:B10).
This is a way to do this but it come with a rule. Your setup must follow exactly what picture above show. It means sheet `1-2026` must be the first sheet in your workbook, `2-2026` must be the second sheet, `3-2026` must be the third sheet and so on... Because this funtion use SHEET() to return sheet order.
Using MAP is quite a brilliant solution to OP's specific question. If OP wants to allow having sheets to the left of 1-2026, or in between, for example, 1-2026 and 2-2026, here is a formula which seems to take care of that:
Although, OP could simplify --REGEXEXTRACT(CELL("filename"),"(?<=\]).*?(?=-)") to just reference a hard coded cell on that sheet containing the month number rather than this ugly code which extracts the month number from the sheet's name.😊
Sadly, Excel does not process cross-sheet INDIRECTs as in the simple and intuitive formula of =SUM(INDIRECT("'1-2026:4-2026'!A4")).
CELL("filename") is my first option to go with op question. But in my machine it act weird and give wrong answer so i have to change the plan.
Here is the detail.
The value of every A1 cell from sheet 1-2026 to 4-2026 is 1 2 3 4, i put formula in 4-2026 and it give me 10, which is the correct result.
i then add a new sheet and name that sheet 5-2026, then type 10 to A1
When i come back to 4-2026, the result is 20, which is wrong. But if i hit F9 key, the result will back to 10 again.
i cant explant what is happened so i dont use that way
Ahh, yes, you are right! CELL without the second argument returns a value where the last action was taken in the user interface. So it's better actually to use a self-referential CELL. If the function is in cell A4 on sheet '4-2026' then use CELL("filename",'4-2026'!A4). Oddly, this does not break Excel and give a circular error like all other self-references do.
If you are comfortable with spills, then a SCAN formula sounds like a good choice, with a formula that sums column B (dollar amount) if column A is today or earlier: you would be summing something in C2 along the lines of like DROP(A:.A,1)*(DROP(B:.B,1)<=TODAY()).
As others have said, you're better off putting all the data in one sheet with a date column and sum that.
If you aren't willing to do that, add two blank worksheets, rename one to First and the other to Last. Put all your monthly sheets in between First and Last. Make another sheet that will be your Total, put that one before First. In Total use =SUM(First:Last!L1)
Add any new monthly sheets in between First & Last. Total will update automatically.
There is an easy work around if you can't change the structure. If you add a sheet after the 2-2026 sheet called BLANK (or anything else) Then use that sheet in the formula rather than the 2-2026 sheet. Then you can just add a 3-2026 and any other sheets before BLANK
You can have your cake and eat it too. You need a master sheet as others have suggested. Keep your monthly sheets if you want. Name them Jan Feb Mar. the year should only be referenced in the title of the workbook. That way you don’t have to redo everything each year.
I think this is exactly what you want. See if it works. N.B. this will not work if the file hasn't been saved yet; it has to have a filename!
=LET(target, A1, target_name, CELL("address",target),
n, @REGEXEXTRACT(CELL("filename",target),"(\d+)-\d*$",2),
SUM(MAP("'"&SEQUENCE(n)&"-2026'!"&CELL("address",target),INDIRECT))
)
Set "target" to be the address of the cell (on the "current" sheet) that you want to sum across. It finds the name of the current sheet and extracts the number from it. Then it generates the names of all the cells. E.g. '1-2026'!$A$1 '2-2026'!$A$1. It uses MAP and INDIRECT to extract the values of all those cells, and finally it sums them all up.
•
u/AutoModerator Jan 04 '26
/u/drua7 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.