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.
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.
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. ruclips.net/video/82mMdIDxQfQ/видео.html 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! 🙂
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.
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
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.
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! 🙂
@@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.
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
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.
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 ruclips.net/video/M44AiVk2COU/видео.html Slicers for PIVOTBY ruclips.net/video/82mMdIDxQfQ/видео.html 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.
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)) .
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
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! 🙂
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!!
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! 🙂
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.
Thanks Ivan! I really appreciate the nice feedback. 😊
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!
This is brilliant, Jon. I can already think of ways of using this in my job.
Ian, London, UK
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.
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. ruclips.net/video/82mMdIDxQfQ/видео.html
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! 🙂
This is a stunning formula, very impressive. I don't have checkboxes available in Excel yet, but can't wait.
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.
Marvellous. Have been looking for this for quite sometime now. Thanks a Zillion!
Thank you for the great video on those advanced formulas. That is fantastic I'll have to study that for a while.
This is excellent. Thanks Jon. It definitely sparked some ideas for current and future projects. Thanks for sharing!!
You are just unbelievable!! Thank you for just being awesome!
Thanks so much, Ken! I really appreciate your support. 🙏
This is just brilliant..Bravo!!! Definitely gonna use it 👏
Awesome! Thanks Adham! I'm happy to hear you're going to use it. 🙌
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
Thanks Patrick! And great point about creating a LAMBDA for this. 👍
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.
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! 🙂
@@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.
Thank you very much. it is indeed amazing tutorial video. Greetings from Ottawa :-)
Thanks so much, Abdullah! I appreciate your support. 🙂
That was brilliant, love the use of Match to move the formula down .
Thanks William! 🙂
The best excel tutorial that I've see in a long time. Love this solition. It will impress my boss. Congrats man
Thanks Mario! I really appreciate the nice feedback and I'm really happy to hear you will be using it to impress your boss! 🙌
Excellent.
Brilliant as always!!!! Greetings from South Africa.
Thanks Chris! 🙌
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
Great video thanks Jon!
Glad you liked it! 😀
Thanks, Great❤
Now we just need a radio button option to compliment the checkbox
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.
I agree, Alfredas! Radio buttons could be very useful. I've put in my request to Microsoft. 🙏😉
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 ruclips.net/video/M44AiVk2COU/видео.html
Slicers for PIVOTBY ruclips.net/video/82mMdIDxQfQ/видео.html
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.
Kindly make a drill down data vba
Without pivot table
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)) .
Fantastic
Thank you! Cheers! 😀
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
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! 🙂
⭐️⭐️⭐️⭐️⭐️
⭐⭐⭐⭐⭐
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!!
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! 🙂