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"). 😊
@Up4Excel3 ай бұрын
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 ( kzfaq.info/get/bejne/otxne8uHzse5Y30.html ) Part 3 ( kzfaq.info/get/bejne/mtl9a7xmlqvQcps.html ). Glad you liked it 👍John
@MGA19a5 ай бұрын
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 ...
@Up4Excel5 ай бұрын
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
@johnborg54195 ай бұрын
Thanks John. :) :)
@Up4Excel5 ай бұрын
Thanks to you John 👍
@jerrydellasala76435 ай бұрын
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!
@Up4Excel5 ай бұрын
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