Thank you for your comments and input. As mentioned by some members of our community, you can use the shift key to select the last sheet. So start typing "=SUM(" then click on the cell you want - then hold down the shift key and click on the last sheet.
I think your formula is better. Bobs formula WOULD have been better if the formula didn't change when executed. When you have a new sheet you still have to be sure it is placed right even though its name is right for the formula. Therefore I think yours is more foolproof but perhaps Excel in time will change so the formula stays with "*Prod*" :)
Ma'am, I am very impressed. Your teachings increase my enthusiasm to learn new things. You have changed my inactive mind in to active. God Bless You!!!
As the number of sheet increases, solving using "SHIFT KET" Is a difficult task and creates confusion practically.. But what maam has taught is more clear and practically no issues.. Especially Bob's 3d formula😍
Thanks for explaining about the quote mark around the name of a tab including a space. It solved an issue I've been having with one of my spreadsheets!
Hey Leila I’ve another tip : you create two empty sheets and put all sheets you want to sum in between, that way even if you have a new sheet, Excel will add them in the selection. The syntaxe of the formula will be : sum(aaa:zzz!B5) aaa and zzz are the first and last sheet of the selection and B5 the cell.
Thanks for the video But the first part is possible to do by selecting the cell in the first sheet - pressing the shift button and then selecting the last sheet In my opinion it is much simpler :)
I love watching your videos. People say that, “Those who cannot do, teach.” But that is not true for you. It’s obvious that you are perfectly capable of being productive using MS Excel, what’s really impressive is that besides your skill and abilities, you are very capable as a teacher as well. I know a man who can make Excel sing, but he cannot teach or explain what he’s doing to someone else to save his life. You are “Excel”ent at both, pun intended. Thank you for all of the information you’ve shared with all of us who have enjoyed watching and learning from your videos. One question about 3D formulas. Can’t you select the cell where you want your formula to reside, press the “=“ key, then select the first sheet and cell with first value, hold down the shift key and simply select the last sheet within your range of sheets. Close your parenthesis and press ENTER? Easy and still dynamic.
Thank you John for the very kind words. Yes - you are absolutely right. You can just select the sheets as well. I got carried away with the syntax explanation :)
Hi Leila.. 3D formulas are great. One trick I use is to bracket the sheets subject to the formula with a left and right blank sheet with the tab names: "START >>" and "
Thank you for sharing your knowledge! I used 3D formulas to consolidate cost center data quite a while ago. For this I used the following trick: I put the sheets between to tabs with the name >. This made the formula easier to read and more robust. Tabs that I wanted to exclude were just dragged out of the section.
This channel is awesome! I was taught this by someone years ago, but I never knew it was called “3D formulas.” We use it to add up individual product or regional P&Ls into a total P&L.
Thanks heaps Leila. You've done a fantastic job sharing your knowledge on RU-vid. Today's trick is gonna save me lots of time. Actually, I'm planning to watch your videos on a a daily basis.
Wow... so far ur’s is the only channel that I hv come across on RU-vid which shares real cool stuff about excel. U r sharing the things that I had dreamed to find out on my own. Rest all are still sharing 20 shortcuts in excel kinda kids stuff. But I feel lucky to hv come across your channel. My optimism levels with what all I can do with excel and data are growing by each day which helps me in my data Analytics job. Many thanks to you and keep up sharing your knowledge. Truly admired. Thank you 🙏
you are Great Leila , there is also another way to Try , = Sum select the first Cell in the first sheet then press Shift and select the first cell in the last sheet ;)
I wanted half my life back when I found these. That wildcard feature is cool: I'll look forward to using it! I would suggest: 1. It is easier to type =SUM( then select the first sheet in the range (Prod Game), then select the cells in that sheet (cell B3), hold Shift key down and click on the last sheet tab in the range (Prod Productivity), and Excel makes the formula automatically. No removing commas, colons etc. 2. I like to add a blank worksheet before the first and after the last sheet in the range (called say Start and End), and make the 3D formula include those sheets e.g. Start!B3:End!B3. Then if I insert a new worksheet between those two, it is automatically included in the 3D reference. The blank sheets won't affect the result. In this example, you have to make sure you add it between Prod Game and Prod Productivity.
@@LeilaGharani I was going to suggest the Shift click method as well. I have also used this with other functions like AVERAGE, it will work with anything that uses ranges. I love the wild card though, that's a great trick.
Hi Leila, so grateful for your tutorials. They have helped me really simplify my spreadsheets. I do food purchases for families in different areas and so putting this into a spreadsheet has helped me especially when I have to break down the food lists into kg's, litres and grams per family, per area. Now that its perfect I can just email the list to the company and have them pack it all up, ready for me to collect. Making up spreadsheets with formulas can be very addictive
Nice. Consolidation of accounts is the application I was thinking of before you mentioned it. Accountants used to prefer Multiplan to Excel because 3d formulas were so easy in Multiplan. Nice to see Excel have that functionality.
Bob's method was terrific. Adding the asterisk on either side of the common sheet names is adding the formula for you. It is for that one instance. If you add more sheets after adding the formula with the sheet name containing the keyword "Prod" in this example, that will not be included dynamically into this formula. Hope that makes sense.
Worked great for my wage totals sheet. I used a blank emp sheet at the end and the 3D formula captured all from 1st to blank. Moving deleted emp sheets after blank and new emp before blank will ensure I'm capturing all the current data. Thanks!
Amazing, this is why I keep believing that anything is possible in Excel. Thank you Leila for bringing such informations. 😎 I wish to be your protege, so much to learn from you.
Excel CAN do anything... you can make it speak, you can control it with a bar-code scanner, I even have an Excel file that will make you a cup of coffee!
Your Videos (Tutorials) has been great help to my profession & never try to miss them, by the way , I'm a Civil Eng professional frm Nepal. This time, I want a Project management plan based on new version MS project, as I've prepare work plan for a multi storey Building. thanks.
one way to stop the accidental insetion of a tab is to have a "Start" and an "End" tab and you sum -- or any other function -- those between, doesnt stop it but can help. or you lock the stucture of the workbook to stop users changing it. Love the masking trick. pity it isnt dynamic to update the formula Great video
Thanks for the tip it is golden! But it seems like the wildcard doesn't work if you insert the "Other_items" in between the sheets after you write the formula with the wildcard
Hello Leila, I have been a secret admirer of your tutorials for about 5/6 months now. I find them very clear and useful to me. I am in project mgt/admin and will like something to manage my Purchases and Requisition, Invoices, Worker's Payroll and Inventory /Stock Taking Can you refer something that would be of benefit? Thanks and keep up this beautiful work.
