Great video! Have been struggling to "display" filter data next to the row and your technique goes a long way in resolving that! Thank you!
@iankr2 күн бұрын
This is brilliant, Jon. I can already think of ways of using this in my job. Ian, London, UK
@KimzCraftz2 күн бұрын
This is a stunning formula, very impressive. I don't have checkboxes available in Excel yet, but can't wait.
@KrishnaKumar-zn9kg2 күн бұрын
Marvellous. Have been looking for this for quite sometime now. Thanks a Zillion!
@RenierWessels2 күн бұрын
This is excellent. Thanks Jon. It definitely sparked some ideas for current and future projects. Thanks for sharing!!
@nsanch0181Күн бұрын
Thank you for the great video on those advanced formulas. That is fantastic I'll have to study that for a while.
@Gorman-84Күн бұрын
Great video. I will never use it as you have shown, but the introduction to all those functions and how they work is very educational. Your presentation was such that I never got bored as you went through it to the end. For years I have avoided tables because they goof up my cell reference formulas. 😁Haha. More recently I am using tables the the advantages of having them in formulas.
@IvanCortinas_ES3 күн бұрын
Very brilliant solution, Jon. You have certainly generated a very Tooltip-like effect with the use of the CheckBox, displaying the desired information depending on the CheckBox selected. I find your contribution very original. Thanks for sharing.
@ExcelCampus3 күн бұрын
Thanks Ivan! I really appreciate the nice feedback. 😊
@adhamm55033 күн бұрын
This is just brilliant..Bravo!!! Definitely gonna use it 👏
@ExcelCampus3 күн бұрын
Awesome! Thanks Adham! I'm happy to hear you're going to use it. 🙌
@kenmcmillan26373 күн бұрын
You are just unbelievable!! Thank you for just being awesome!
@ExcelCampus3 күн бұрын
Thanks so much, Ken! I really appreciate your support. 🙏
@patrickschardt77243 күн бұрын
Wow that's a fantastic example of check boxes ✅ and dynamic array functions If this were to be used again and again, it could be made into a LAMBDA / LET function
@ExcelCampus3 күн бұрын
Thanks Patrick! And great point about creating a LAMBDA for this. 👍
@abdsab783 күн бұрын
Thank you very much. it is indeed amazing tutorial video. Greetings from Ottawa :-)
@ExcelCampus3 күн бұрын
Thanks so much, Abdullah! I appreciate your support. 🙂
@GeertDelmulle3 күн бұрын
Hey Jon, here’s a quick and small simplification: instead of DROP, you cam simply do a -1 in the rows of the expand. Overall a very cool use case - will try to integrate the technique where appropriate.
@ExcelCampus3 күн бұрын
Thanks Geert! I actually had a segment in the video on this but cut it out to keep the video a little shorter. In my original solution I used XMATCH() - 1. The issue is when the checked box is in the first row. In that case, xRow = 0. Returning a zero to INDEX returns an error. So originally, I had an additional IF statement to handle that scenario. Then I realized DROP would allow me to simplify the formula by allowing XMATCH to be zero. Maybe I'll do a follow-up video to walk through some of those lessons learned along the way. It's probably good to know that this formula took several iterations to get it simplified. I didn't nail it on the first attempt... I hope that helps. Thanks again and have a nice day! 🙂
@GeertDelmulle3 күн бұрын
@@ExcelCampus Hey Jon, thanks for the response! I just shot from the hip smashing my keyboard on my iPad. Did not do the test. Usually that doesn’t end well for me… :-) So, thanks for pointing out that checking the boundaries points us in a different direction - good point! A more in depth video is always welcome for people like me, but others may be put off by the nitty-gritty. I welcome the suggestion. Cheers.
@ryanbartlett6723 күн бұрын
Nicely done! Thank you. You illustrated some cool and complex topics, but walked it through in an easy to follow, structured manner. Idea for future video: filter for multiple checkboxes checked.
@ExcelCampus3 күн бұрын
Thanks Ryan! I'm happy to hear it was easy to follow. Explaining a formula like this isn't always easy... That's a great idea for a future video! I actually have a video that covers that technique. It's what I call a "grid slicer" for the new PIVOTBY function. kzbin.info/www/bejne/bmPQfpd_ed2EnLM However, I can see how that solution is probably more complex/advanced and the use case is a bit limited compared to what you are talking about. I'll add your idea to our list for future videos. Thanks again and have a nice day! 🙂
@josilhus3 күн бұрын
The best excel tutorial that I've see in a long time. Love this solition. It will impress my boss. Congrats man
@ExcelCampus3 күн бұрын
Thanks Mario! I really appreciate the nice feedback and I'm really happy to hear you will be using it to impress your boss! 🙌
@chrism90372 күн бұрын
Great video thanks Jon!
@ExcelCampus2 күн бұрын
Glad you liked it! 😀
@williamarthur48013 күн бұрын
That was brilliant, love the use of Match to move the formula down .
@ExcelCampus3 күн бұрын
Thanks William! 🙂
@ExcelWithChris3 күн бұрын
Brilliant as always!!!! Greetings from South Africa.
@ExcelCampus3 күн бұрын
Thanks Chris! 🙌
@tranvinhthinh88252 күн бұрын
Thanks, Great❤
@Alfredas-u7x3 күн бұрын
Now we just need a radio button option to compliment the checkbox
@simonhesselstrand14423 күн бұрын
Allmost we need alsoI a "master" chekboxes, a checkbox that change values off sub chekboxes so I can select a range of checkboxes to true or false. That will need the checkbox to have a third state when just some sub checkboxes are checked but not all.
@ExcelCampus3 күн бұрын
I agree, Alfredas! Radio buttons could be very useful. I've put in my request to Microsoft. 🙏😉
@ExcelCampus3 күн бұрын
That would be very useful, Simon! I've come up with some workarounds to select all checkboxes with a link, then press spacebar to check/uncheck all. Those are in my other videos on checkboxes. 19 Uses for Checkboxes kzbin.info/www/bejne/g2WXcpyMoJd2hbc Slicers for PIVOTBY kzbin.info/www/bejne/bmPQfpd_ed2EnLM But it's a workaround that doesn't fully achieve the functionality you are talking about. Right now, we could do that with VBA. However, it would be nice to have a solution that doesn't require VBA.
@JoshuaArjoon-l6x2 күн бұрын
this was an awesome course excel campus continue making more videos everday that was the best. Because everyday i advanced my self in watching all my tech videos and it takes me a hour an a half to watch all my tech videos. But how this was a course i left this for last to watch because this an important excel course tutourial to listen to an pay a little more attent because a course is much longer to watch so i have to pay proper attention to this tutourial also i do that to all your videos from Joshua R Arjoon i am a subscriber to your channel for a while before i was using my moms account lcindy but now all my tech videos i transfer them to my account now
@johndurran6143 күн бұрын
Excellent.
@yousrymaarouf29312 күн бұрын
Fantastic
@ExcelCampus2 күн бұрын
Thank you! Cheers! 😀
@shaharyarahmed61242 күн бұрын
Kindly make a drill down data vba Without pivot table
@williamarthur4801Күн бұрын
Just to add I don't think expand can deal with a single cell, so if you were just working on a single column you can use CHAR( SEQUENCE( match , , 10, 0)) .
@rubenmunozverdu75287 сағат бұрын
Does expand let you use 0 rows? I mean, instead of using drop you could just expand to xRow-1, but then maybe that would be a problem for the first checkbox...
@richardhay6453 күн бұрын
Maybe TRIMRANGE could be used to control expansion rather than selecting extra rows and controlling with IFERROR? Also can we used XLOOKUP reater than INDEX with XMATCH? WITH
@ExcelCampus3 күн бұрын
Ooooo interesting idea with TRIMRANGE! I'm not sure it will work with conditional formatting yet. I believe we need to be able to reference spill ranges in the B4# in the Applies To field in conditional formatting. You can use the reference initially, but Excel converts it to a static range. Hopefully that will change in the future. XLOOKUP can be used instead of INDEX/XMATCH for the initial lookup. However, we need the row number for DROP, so that is why I used XMATCH. With that said, you can use ROW(XLOOKUP()) to get the row number for the value that XLOOKUP returns. That is the row number of the sheet number and not the row number of the table, so you would need to subtract the row number of the header and add 1. Extra work, but technically possible and good to know that ROW works with XLOOKUP. I hope that helps. Thanks again and have a nice day! 🙂
@redhaakhund1271Күн бұрын
⭐️⭐️⭐️⭐️⭐️
@rajeshmajumdar4999Күн бұрын
⭐⭐⭐⭐⭐
@Quidisi3 күн бұрын
Holy cow! That was brilliant! Also, 15:44 (your M4 formula) just helped me solve another issue. I have a complex spreadsheet that checks for errors and alerts the user, but I couldn't figure out how to tell the user which row it was on (they had to scroll down and look for the conditional formatting). Well done. Very impressive stuff!!
@ExcelCampus3 күн бұрын
Thanks Quidisi! I'm really happy to hear that you'll be able to use that row technique. That's a great idea to use it for errors. Thanks for sharing how you will be using it. It took me a bit of time to figure it out for this solution, but I do think it could be useful for many other solutions, like highlighting errors or auditing spreadsheets. I'll do a video in the future that covers the technique in more detail since it's a bit buried at the end of this video, and I didn't go into a full explanation. Thanks again and have a nice day! 🙂