My job recently asked for my monthly totals, which were always done in a Word doc, to be sent as one file for comparison each month. I figured there had to be an simpler way in Excel. This is just what I needed. You explanation is straight to the point and easy to follow! Only 2 minutes in and I was able to set up my sheets. Thx a much!!!
Great🎉 Ive been finding tips on how to get sum across multiple sheets.Yikes thank you this is great I have totally learned a lot. But can I ask a question ? for instances like getting the max from Jan to March could I use this formula by just typing equal sign and then max word?
Yes, you can just type =MAX( into a cell to start the formula. The challenge is getting the right syntax for referencing the cell across all the sheets.
Hi Mark. Great video! I remember when I first learned about this feature.. saved so much time.. but as you discussed, you must be very careful to maintain a consistent structure for each sheet. That said, it can be a very useful technique to quickly create a summary when you have the right circumstances and format. Thanks for sharing :)) Thumbs up!!
Thanks Wayne. I agree. It's great in the right circumstances, but there are dangers if people don't know what they are doing (I'm sure I've made all those errors at some point in the past 😊)
brilliant Job, very useful and well explained. i've a question here, what if we have tabs which is not subsequent and have different names with a key word I.E Budget Jan 22 / Budget feb 22.. etc.....but this tab is not beside each other as we would have tabs not to be picked? can it be done by the key word Budget?
great easy to understand video. I'm a novice and I am trying to add previous sheet total throughout workbook. Keeping a month to month annual total. any tips?
I had to do something similar, but week to week totals adding to year to date. I used two workbooks. In my year to date column I used something like =sum(start:end!B2,B2) and just put the end page right before my current week. That way it added all of the cells plus the current week, then I copied the current worksheet data with the year to date info into another workbook so each week would be current for that week only.
@@ExcelOffTheGrid no problem at all. always happy to support my fellow content creators. I am looking forward to improving on my own current video format I have uploaded and, in the future, trying to create content as good as yours in the long term.............. ill save some haahah
What about for instances where the cell addresses are inconsistent ? is it possible to assign a unique key field that can be used as cell references for summation across the worksheets ?
I'm curious. Can I use a master sheet with data and link that data to go into multiple sheets? I run a 24 team baseball league and use excel for all data. Each team has a tab for it's players and salary. I wanted to use a master sheet with all the players and the players salary and link that to all the 24 team tabs. So if a trade occurs I could just switch the teams in the master sheet and it will then automatically swap those players in the individual team tabs
Hi .... nice information. But, I am getting this error message "Error Unknown range name: 'APRIL23'." Here, APRIL23 is name of the worksheet. I want to sum the monthly wise attendance of the students from APRIL-23 to MARCH-24. Will you please help me.
Why can't we just specify the same cell Once in the case all of the sheets have the same layout which is probably the case. I found it painful to have to enter cell D2 over and over 12 times for the year. The =SUM(Sheet1:Sheet12!D2) formula generates an error even though my sheet names are correct and the D2 cell itself contains a formula in each sheet.
can someone tell me how to deal with hidden sheet? I want to sum with same cell and different sheet like this but I have lots of hidden sheet. It would take many times if I keep repositioning them to analyze the data.
Still dont work for me, made 1 sheet named start, one named end, enter the formula =sum(start:end! D28), and get the message the zone named start not found. Then i tried sum('start:end'! d28), and get error, the sheet named start:end dont exist.. I dont understand why..
My job recently asked for my monthly totals, which were always done in a Word doc, to be sent as one file for comparison each month. I figured there had to be an simpler way in Excel. This is just what I needed. You explanation is straight to the point and easy to follow! Only 2 minutes in and I was able to set up my sheets. Thx a much!!!
Woop woop 💥
Great news Bernadette. Thanks for letting me know. 😀
Watched multiple videos and read a lot of articles and this is the only one that worked immediately. Thank you so much!
You're welcome!
Loved the idea of inserting "Start" and "End" sheets. Thank you so much.
Glad it was helpful!
Great explanation. It took me ages before I came across your tutorial, getting nowhere on so many other sites. You made it so simple.
You're welcome! Glad it helped.
Thank you fir this video. Made what the college professor couldn't properly explain to me much easier. Thanks!
Lol! Glad I could help. 😀
Great🎉 Ive been finding tips on how to get sum across multiple sheets.Yikes thank you this is great I have totally learned a lot. But can I ask a question ? for instances like getting the max from Jan to March could I use this formula by just typing equal sign and then max word?
Yes, you can just type =MAX( into a cell to start the formula.
The challenge is getting the right syntax for referencing the cell across all the sheets.
great trick. it is so flexible to include or exclude sheets that involve the operation.
Hi Mark. Great video! I remember when I first learned about this feature.. saved so much time.. but as you discussed, you must be very careful to maintain a consistent structure for each sheet. That said, it can be a very useful technique to quickly create a summary when you have the right circumstances and format. Thanks for sharing :)) Thumbs up!!
Thanks Wayne.
I agree. It's great in the right circumstances, but there are dangers if people don't know what they are doing (I'm sure I've made all those errors at some point in the past 😊)
Wooo!
3 Dimensional formulae.
Agree with Wayne below which is why I use PQ for this now.
Yes, I agree. PQ is definitely a more robust solution for handling data.
brilliant Job, very useful and well explained.
i've a question here, what if we have tabs which is not subsequent and have different names with a key word I.E Budget Jan 22 / Budget feb 22.. etc.....but this tab is not beside each other as we would have tabs not to be picked? can it be done by the key word Budget?
You would need to use Power Query for that scenario. Check out my Power Query series here: exceloffthegrid.com/power-query-introduction/
OMG this saved me so much time. Thank you.
You're welcome!
Thank you ! Thank you! Thank you! Your video was easy to follow and got the job done!
Great to hear!
So well explained! Thank you!
Glad it was helpful! 😁
great easy to understand video. I'm a novice and I am trying to add previous sheet total throughout workbook. Keeping a month to month annual total. any tips?
🤔 Hmmm…. Interesting scenario.
I think that will probably require the INDIRECT function to call a different range of sheets for each running total.
I had to do something similar, but week to week totals adding to year to date. I used two workbooks. In my year to date column I used something like =sum(start:end!B2,B2) and just put the end page right before my current week. That way it added all of the cells plus the current week, then I copied the current worksheet data with the year to date info into another workbook so each week would be current for that week only.
Simply very very helpful advices about layout and nice explanation about Excel structural layout. Essential knowledge about Excel.
Thanks a lot.
Glad it was helpful! 👍
Adding Start and End helped a lot since my sheets are date named. Thank you
Yes, ‘Start’ and ‘End’ definitely help to simplify things. 👍
This was an amazing help -- THANK YOU :)
You’re welcome. 👍
Exactly what I was looking for. :)
Glad I could help!
phenomenal content Excel Off The Grid. I killed that thumbs up on your video. Always keep up the excellent work.
Lol!! Thank you. Don’t use up all those thumbs up, you should save same for my other videos 👍
@@ExcelOffTheGrid no problem at all. always happy to support my fellow content creators. I am looking forward to improving on my own current video format I have uploaded and, in the future, trying to create content as good as yours in the long term.............. ill save some haahah
Thank you! Such a clear explanation.
Thanks Chris.
I am trying =SUM(Load1:Load2!B3) and it's not working for me. Why? 😭
You’ve missed the single quotes around the sheet names.
Wow! So helpful. Thank you very much!
You're very welcome!
hello sir if we have different cell in different sheet how can i sum?
Great help! Thank you so much!!
Excellent video!
Thank you very much!
u can use consolidate icon in Data Ribbon
can this be used for Sumif where I have references from multiple sheets?
No it won't work with SUMIFS.
You will need to load the sheets into Power Query; Close & Load to a Table. Then you will be able to use SUMIFS.
Thank you!
What about for instances where the cell addresses are inconsistent ? is it possible to assign a unique key field that can be used as cell references for summation across the worksheets ?
If the cell address aren't consistent, you'll need to use Power Query to combine the data.
I'm curious. Can I use a master sheet with data and link that data to go into multiple sheets? I run a 24 team baseball league and use excel for all data. Each team has a tab for it's players and salary. I wanted to use a master sheet with all the players and the players salary and link that to all the 24 team tabs. So if a trade occurs I could just switch the teams in the master sheet and it will then automatically swap those players in the individual team tabs
Based on what you have described, I don’t think this method will do what you want.
Thank you! :)
Super sir
Hi .... nice information. But, I am getting this error message "Error
Unknown range name: 'APRIL23'." Here, APRIL23 is name of the worksheet. I want to sum the monthly wise attendance of the students from APRIL-23 to MARCH-24. Will you please help me.
Why can't we just specify the same cell Once in the case all of the sheets have the same layout which is probably the case. I found it painful to have to enter cell D2 over and over 12 times for the year. The =SUM(Sheet1:Sheet12!D2) formula generates an error even though my sheet names are correct and the D2 cell itself contains a formula in each sheet.
You’re missing the single quote characters around the sheet names.
Thank you helpful...
Thanks, glad I could help.
so how to sum different cell across multiple sheets by searching with the part number?
You can’t using this method. You would need to:
- Power Query into a single data source
- Load as Table
- Perform SUMIFS on the Table.
If the sheets are in different format then how we can sum?
In that scenario, Power Query might be a better option.
can someone tell me how to deal with hidden sheet? I want to sum with same cell and different sheet like this but I have lots of hidden sheet. It would take many times if I keep repositioning them to analyze the data.
Unhide all the sheets, and move them outside the Start and End sheets.
It's all about your management of the workbook.
did not work... i had two tabs - help.....please!!!
The formula gets messed up after inserting new rows
Yes, if the sheets don’t stay in sync then can’t work.
this only works if your sheet name doesnt have spaces...
=SUM(Week 13:week 49!I11) doesnt work for me :(
You haven’t referenced the sheets properly. If there is a space, sheet names need to be enclosed in single quotes.
=SUM(‘Week 13:Week 49’!I11)
did you try the START & END tabs, that worked for me
@@RunwayCats did u find solution? Having the same issue
Still dont work for me, made 1 sheet named start, one named end, enter the formula =sum(start:end! D28), and get the message the zone named start not found. Then i tried sum('start:end'! d28), and get error, the sheet named start:end dont exist.. I dont understand why..