Great information! Looking forward to part 2. I realized the month on the Report sheet could be populated dynamically using the function: Text($O$2*28,"mmmm"). 😊
Great point and thanks for the dynamic month function 👍Part 2 (and 3) already out so in case you need it here's the links: Part 2 ( th-cam.com/video/lv7HhViaU3I/w-d-xo.html ) Part 3 ( th-cam.com/video/dsM8Y51ElBg/w-d-xo.html ). Glad you liked it 👍John
Nice for beginners but … if working with this kind of data in the background, you could solve this more efficiently and elegant through PQ and PowerPivot table (two measures and off you go), and it would be more scalable i guess ...
You're definitely correct there, and that's what the next 2 videos in the series will be covering so watch out for them. Basically I'm taking people there in stages 😉 I end up with a PowerPivot model of the same data and report as you say 👍 John
For your Month Actual formula, any reason for using "SUMIFS(tblActuals[Order Value],tblActuals[Region],$B6,tblActuals[Month],$O$2)" and copying it down rather than using "SUMIFS(tblActuals[Order Value],tblActuals[Region],$B6:$B11,tblActuals[Month],$O$2)" which would spill the results with fewer formulas? Locked the ranges to be able to copy left and right. Also, rather than having to switch back and forth to reference tables, the table names will be found if you start typing them (good reason to name them "tbl..."!), and once the entire table name is selected, typing "[" will bring up the column names. I find that much easier than flipping back and forth!
Great tips Jerry and thanks for adding them 👍. I didn't think about the spilled range option so thanks, although it won't work on older versions of Excel so anyone reading this bear that in mind. On jumping between sheets I actual use all shortcut keys in practice so pretty quick for me, but I like your ideas so I'm going to test them out as they might be faster 👍 John
Great information! Looking forward to part 2. I realized the month on the Report sheet could be populated dynamically using the function: Text($O$2*28,"mmmm"). 😊
Great point and thanks for the dynamic month function 👍Part 2 (and 3) already out so in case you need it here's the links: Part 2 ( th-cam.com/video/lv7HhViaU3I/w-d-xo.html ) Part 3 ( th-cam.com/video/dsM8Y51ElBg/w-d-xo.html ). Glad you liked it 👍John
Thanks John. :) :)
Thanks to you John 👍
Nice for beginners but … if working with this kind of data in the background, you could solve this more efficiently and elegant through PQ and PowerPivot table (two measures and off you go), and it would be more scalable i guess ...
You're definitely correct there, and that's what the next 2 videos in the series will be covering so watch out for them. Basically I'm taking people there in stages 😉 I end up with a PowerPivot model of the same data and report as you say 👍 John
For your Month Actual formula, any reason for using "SUMIFS(tblActuals[Order Value],tblActuals[Region],$B6,tblActuals[Month],$O$2)" and copying it down rather than using "SUMIFS(tblActuals[Order Value],tblActuals[Region],$B6:$B11,tblActuals[Month],$O$2)" which would spill the results with fewer formulas? Locked the ranges to be able to copy left and right.
Also, rather than having to switch back and forth to reference tables, the table names will be found if you start typing them (good reason to name them "tbl..."!), and once the entire table name is selected, typing "[" will bring up the column names. I find that much easier than flipping back and forth!
Great tips Jerry and thanks for adding them 👍. I didn't think about the spilled range option so thanks, although it won't work on older versions of Excel so anyone reading this bear that in mind. On jumping between sheets I actual use all shortcut keys in practice so pretty quick for me, but I like your ideas so I'm going to test them out as they might be faster 👍 John