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*" :)
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😍
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!!!
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 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.
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!
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 "
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.
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.
Thanks heaps Leila. You've done a fantastic job sharing your knowledge on CZcams. Today's trick is gonna save me lots of time. Actually, I'm planning to watch your videos on a a daily basis.
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 ;)
Wow... so far ur’s is the only channel that I hv come across on CZcams 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 🙏
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.
For those that find themselves referencing multiple sheets and looking to avoid the pitfalls in the video, I would recommend the Extra Step(s) of Creating a Named Range.
Starting to really love named ranges and xlookup formulas. So much easier to know what block of data you working with if its named. Especially when working with big sets of data.
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!
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!
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
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.
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.
As a bonus, you could create a summary sheet at the beginning that has a drop down menu populated with the sheet titles so that it displays the information on the selected sheet with a couple clicks of the button. You can even include the “All Products” sheet in the drop down. Thank you for the video.
@@LeilaGharani Thank you for all your great content, Leila. I just purchased your course yesterday, and I am excited to see what you put together for us ❤️
Video uploaded more than 1 year ago and youtube offered it to me while i am loafing on the home page. Then i am seeing a comment that is written just a day ago. This cannot be just coincidence. I think i will need and use these useful informations soon 😊 thanks good people who likes to share their info like me ✌
Grab the file I used in the video from here 👉 pages.xelplus.com/3d-formulas-file
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*" :)
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😍
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!!!
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 :)
Exactly!
Great
Thanks bro
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.
Thank you for the suggestions John!
@@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.
yes, i apply this same way as John Ford. it is easier and less prone to errors. eila, thanks for this video
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!
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 :)
This is a great tip! I hated the multi-sheet click. Didn't even think that the range type would work on sheets. Now I know. Thanks Leila.
Glad you like the tip Chris.
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 "
Absolutely agree. These visual reminders are very helpful. And Shift is a quick way to reference. Thanks for your input!
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.
Glad you like it. That's exactly how we used it in my corporate past too :)
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.
Cr
Ohhhhh, The more videos I see the more I fall in love with you. You are the best!
Oh my.. U are the best Excel Teacher ...who knows students requirement. Thanks Leila. U hve saved so much of my time and stress.
Thanks heaps Leila. You've done a fantastic job sharing your knowledge on CZcams. Today's trick is gonna save me lots of time. Actually, I'm planning to watch your videos on a a daily basis.
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 ;)
Great tip, thanks!
Wow... so far ur’s is the only channel that I hv come across on CZcams 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 🙏
Lelia your videos are awesome..i like the way you explain things...
Thanks for the 3-D fun, Leila!!!
You‘re very welcome Mike :)
Hi Sir Girvin, can I use 3D references with SUMIFS ?
Thanks in advance.
Thanks, First way can be don by holding shift but bonus was amazing.
Love it.
Thanks for the tip.
This is so cool. I have to consolidate the budgeted p&l of 32 branches. This really made my task simple. Thank you so much
czcams.com/video/L3WTCh1dRKg/video.html
I had been trying to understand about 3-D Reference for 6 months but no one have maked me understand.
Thanks a lot Leila you have described nicely...
Glad it's helpful Faisal.
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
I'm glad to hear that. It's great you could implement your knowledge to be more efficient.
Sometimes it's the little tricks that can save the average person a LOT of time. This is one of them! Thanks!
Glad it was helpful!
I've done multiple sheet calculations similar to this but those hacks were ridiculous! Thank you!
Thanks Leila. I knew the 3D formula. I had been using wildcard filtering in Power Query as well. But never imagined I could combine it. Brilliant!
Great trick! Thanks!
Note to myself:
=SUM(‘Prod Game:Prod Productivity’!B3)
=SUM(‘Prod Game:Prod Productivity’!B3:B8)
=SUM(‘*Prod*’!B3)
Bob's trick is classic Bob - simple but very effective.
Thanks Leila for 3D formulas. I am big fan of yours as your videos have something different all the time.
I have watched many videos...but this is something outstanding
Thank you! Cheers!
Marvelous!! I am really surprised 😊 to know about this type of SUM formula uses. I really appreciate your efforts.
Thank you Mam for sharing this.
My pleasure!
Marvelous, it reiterates that we need to keep our thinking cap on while working with excel
I never take it off :)
Omg. So many years with the wrong way. Thx a lot excel Queen!!!❤️❤️❤️❤️
You‘re welcome 😊 better late than never.
Your videos are clear, well planned and very informative. Thank you!
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.
Thank you Leila and Bob for these tricks.
For those that find themselves referencing multiple sheets and looking to avoid the pitfalls in the video, I would recommend the Extra Step(s) of Creating a Named Range.
Starting to really love named ranges and xlookup formulas. So much easier to know what block of data you working with if its named. Especially when working with big sets of data.
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!
Thanks so much for the 3-D formula, Leila, you saved lots of my time.
Glad it was helpful!
Many did well but you excel Leila… bonus is awesome…!!! Thank you .. I love it
Glad you like it!
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.
OMG!! What a bonus is it?, thank you Bob. And thank you leila.
I am using it for the last 6yrs... but using '*' sign to combine in such a way you used it is new for me. Thank :)
Thank you for sharing this super neat trick! I can't wait to share it with my colleagues!
Great! Your videos are really useful and very easy to understand !
Wow, awesome. Bob's idea is so nice!
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!
Glad it helped James.
I seriously am at a loss of words. This is an awesome trick. Thanks a lot.
Glad you like it :)
And Thanks to you leila for being such a beautiful teacher! Always love your videos👌
That's good to hear :)
You are very hardworking and passionate for sharing of your knowledge and skills.
Very useful formula for complex sheets.
This is going to save me a sheetload of clicking. Thank you
Hilarious
Thanks From Turkey. Very nice tip.
Leila, that's going to change the way I work with Excel! Thanks for sharing this!
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
Once again Miss Leila......... Brilliant
Great tip! I am thinking of color coding like sheets to give a hint that an errant sheet was inserted in the range.
Cool trick! Thanks for sharing! Nice video as usual!
Thanks for that video. You simplify my daily task on excel. BIG Thanks !!!
My pleasure. Glad it's helpful.
This is a great tip for me. Thanks, Leila.😄
Really appreciate your research and generous to bring us Madam Leila
Very nicely explained.
Wow Thanks..! you made future journey more easy..!
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.
Wow! Now "stars shining bright above..." me🎶 🤣 I didn't know I can use * in formulas calling sheets! Thanks for this and for all of you. 👏
Sum based on part of Sheet Name s is really awesome..I never know about it as of now.. Thanks Bob and Leila..
Wonderful trick, thanks Leila
You, Ma'm, just saved hell lot of my time. Thanks.
Glad to help!
That's Great. It will save huge time of mine. Thanks a lot.
Thanks LG for another cool trick, we always learn something new from you.
Thumbs up 👍
Wow Lelia, that is an amazing discovery. Thanks to Bob as well for that! You make me look good!!!! 8-)
Sorry I can't speek full english!
But about this video clip,
Very very nice !
Thank you so much for your teaching of excel.
Thanks again!
Great video, with great comments, tips and tricks!
!(: Thanks :)!
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
Awesome video. Thank you Leila, new subscriber here. This will save a lot of time.
Glad you like it. It's great to have you here.
very practical, just saved me with a file of 26 sheets
I love it thank you so much Ms. Leila
Thanks for video, i always love your teachings
That’s a really neat trick.
What a time saver! Thanks Leila and Bob 😃
Angel of Excel, thanks :)
😘
Thanks Leila. Very useful
Brilliant and useful tip as always! Thanks Leila & Bob!
Our pleasure Steve :)
Thanks for this helpful video.
thank you for your informational content..it always help me from my work..lovelots!
So glad!
So awesome! Can this in anyway be combined with dynamic arrays (like with filter)
You know what they say about Bob Umlas ? They say he is the emperor of Excel. We can call him Your Majesty :-D.
That is so beautiful!!. Thanks for sharing
Thank you! Cheers!
Thanks a ton Laila ....very useful
Thanks for your great videos
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.
As a bonus, you could create a summary sheet at the beginning that has a drop down menu populated with the sheet titles so that it displays the information on the selected sheet with a couple clicks of the button. You can even include the “All Products” sheet in the drop down.
Thank you for the video.
Thanks for the tip, Joseph!
@@LeilaGharani Thank you for all your great content, Leila. I just purchased your course yesterday, and I am excited to see what you put together for us ❤️
Video uploaded more than 1 year ago and youtube offered it to me while i am loafing on the home page. Then i am seeing a comment that is written just a day ago. This cannot be just coincidence. I think i will need and use these useful informations soon 😊 thanks good people who likes to share their info like me ✌
Can we create a drop-down menu automatically that includes sheet names as choosing sheets? @joseph coon
@@Sekoleyte I am sure. I’m just getting out of bed, so give me a bit to research it. 😃
Thanks Leila. Love the bonus tip.
Leila & Bob: That is so useful. Thank you !
Thank you so much for sharing.
Great video Leila! Thanks!
You're so welcome!
very useful and valuable content and so smart on your way of explanation as usual ❤❤❤
Thanks for the Video. It's very useful.
Thanx Leila..u r an excellent teacher and off course beautiful too.....