The Show Details Checkbox: Advanced Excel Formulas

[@gandhisunil3]

Thanks a ton Jon🎉 Today implemented this in one of our real time data.. Super excited to see the working of Expand function

[@IvanCortinas_ES]

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.

[@ExcelCampus]

Thanks Ivan! I really appreciate the nice feedback. 😊

[@franchiseteam2595]

This has solved a problem I'd been struggling with, summarising a list of supporting documents relevant to each of our organisational policies. Works perfectly. Thanks so much.

[@KrishnaKumar-zn9kg]

Marvellous. Have been looking for this for quite sometime now. Thanks a Zillion!

[@iankr]

This is brilliant, Jon. I can already think of ways of using this in my job. Ian, London, UK

[@williamrhopkins]

Nice step by step explanation. I liked your use of Expand to position the results.

[@JimFleming-d9n]

This is another outstanding video from Jon at ExcelCampus. This is much easier than using drilldown feature in pivot tables. When you have time, would like to see how you would handle when the columns are not side by side. Many thanks again Jon!

[@Geir49]

This is also my issue: Retrieve columns that are nor side by side.

[@Adam_K_W]

Ohhhhhhh MAN! I have a use case for this!! I need to check if my company has the version of excel with the check boxes. Thanks Jon!!

[@JoshuaArjoon-l6x]

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

[@twarren3685]

I'm so excited about using this technique to add options for my team to view specific data. I'm practicing, practicing, practicing! Thank you for another great tutorial!

[@ExcelCampus]

Sounds great! 😀

[@KimzCraftz]

This is a stunning formula, very impressive. I don't have checkboxes available in Excel yet, but can't wait.

[@adhamm5503]

This is just brilliant..Bravo!!! Definitely gonna use it 👏

[@ExcelCampus]

Awesome! Thanks Adham! I'm happy to hear you're going to use it. 🙌

[@Quidisi]

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!!

[@ExcelCampus]

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! 🙂

[@ryanbartlett672]

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.

[@ExcelCampus]

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! 🙂

[@josilhus]

The best excel tutorial that I've see in a long time. Love this solition. It will impress my boss. Congrats man

[@ExcelCampus]

Thanks Mario! I really appreciate the nice feedback and I'm really happy to hear you will be using it to impress your boss! 🙌

[@williamarthur4801]

That was brilliant, love the use of Match to move the formula down .

[@ExcelCampus]

Thanks William! 🙂

[@patrickschardt7724]

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

[@ExcelCampus]

Thanks Patrick! And great point about creating a LAMBDA for this. 👍

[@MD-X]

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!

[@ExcelCampus]

Glad it helped! 😀

[@RenierWessels]

This is excellent. Thanks Jon. It definitely sparked some ideas for current and future projects. Thanks for sharing!!

[@kenmcmillan2637]

You are just unbelievable!! Thank you for just being awesome!

[@ExcelCampus]

Thanks so much, Ken! I really appreciate your support. 🙏

[@abdsab78]

Thank you very much. it is indeed amazing tutorial video. Greetings from Ottawa :-)

[@ExcelCampus]

Thanks so much, Abdullah! I appreciate your support. 🙂

[@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.

[@ExcelCampus]

Thanks Gorman! And never say never. 😉 Just kidding. I'm happy to hear that you enjoyed the presentation. I'm also happy to hear you are using Tables more often. I completely agree with you on the formulas, and also avoid Tables in certain scenarios. Mostly when I'm sharing files with other users that aren't familiar with Tables yet. I do have a separate video on that topic. kzbin.info/www/bejne/b5-mn6qoeriHY8U But don't let that video scare you away from using Tables. It's just good to know why many users avoid them or don't like them. I hope that helps. Thanks again and have a nice weekend! 🙂

[@extraktAI]

This is incredible, thank you!

[@ExcelWithChris]

Brilliant as always!!!! Greetings from South Africa.

[@ExcelCampus]

Thanks Chris! 🙌

[@GeertDelmulle]

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.

[@ExcelCampus]

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! 🙂

[@GeertDelmulle]

@@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.

[@ExcelCampus]

Haha! No worries at all! I really appreciate the feedback and questions, as it helps me and others learn different ways to do things in Excel. There are always so many different possibilities for one solution. Thanks again and have a nice weekend! 🙂

[@chrism9037]

Great video thanks Jon!

[@ExcelCampus]

Glad you liked it! 😀

[@tranvinhthinh8825]

Thanks, Great❤

[@nsanch0181]

Thank you for the great video on those advanced formulas. That is fantastic I'll have to study that for a while.

[@andreyslavchev722]

This is absolutely great! I've just copied the formula for a project of mine. Thanks, Jon. I was wondering if you could modify the formula in such a way that the details are still visible if you put a filter in one of the columns. For example, you want an overview of all the orders from July 13, filter by that date in the date column and then select the checkbox for order 202121 so you still see the 5 items for that order. Hope that makes sense. But, thank you so much again for this fantastic solution.

[@johndurran614]

Excellent.

[@petecullan3113]

Genius!!!

[@ronaldoliver1700]

Thanks

[@yousrymaarouf2931]

Fantastic

[@ExcelCampus]

Thank you! Cheers! 😀

[@williamrhopkins]

Pretty nifty solution. One limitation is that if you filter the Orders list then the detail arrays spill into hidden rows and are not visible. Sorting is fine so if you want to look at one company's orders you can sort the Orders table. Any ideas on how to overcome the filtering limitation?

[@shaharyarahmed6124]

Kindly make a drill down data vba Without pivot table

[@Alfredas-u7x]

Now we just need a radio button option to compliment the checkbox

[@simonhesselstrand1442]

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.

[@ExcelCampus]

I agree, Alfredas! Radio buttons could be very useful. I've put in my request to Microsoft. 🙏😉

[@ExcelCampus]

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.

[@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)) .

[@ExcelCampus]

Hi William, EXPAND can deal with single cells, or 1x1 arrays. However, in the video I forgot to mention that you have to put an apostrophe in the cell. If the single cell is blank then EXPAND returns a #VALUE error. So putting an apostrophe in the cell gives it a value, and the cell will appear blank. I hope that helps. Thanks again and have a nice weekend! 🙂

[@ExcelCampus]

BTW I forgot to mention that CHAR(10) is a cool workaround. Thanks for sharing! 🙌

[@richardhay645]

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

[@ExcelCampus]

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! 🙂

[@DavidBrault-r5d]

If you change a header in the details, how do you change the formula in the Show Details so that it displays the new header?

[@Geir49]

I copied your formula and translated to my tables. It work fine except for one issue: My "TableSales" has 24 columns, but I only want to retrieve 6 of them. I have tried many times, but are not Abel to fix it. It always comes out with an error that I am not able to solve. Please set me on track!

[@rashah70]

Hi , How can automatically click checkbox in entire raw with the colour (Blue for High & Red for Low Touch) when (( B1 is equal to C1 (High) (B1=C1)) or (( B1 is Equal to D1(Low) (B1=D1)) in first time and then I manually remove checkbox colour will disappear I AM IN STOCK MARKET SO WHEN CURRENT PRICE COME TO DAYS HIGH OR LOW SO I CAN easily FIND OUT FROM LIST thanks

[@rashah70]

1. Assume: Cell A1 is the current traded price, which will fluctuate with updates. Cell B1 is the intraday high price. Also fluctuate Cell C1 is the intraday low price. Also fluctuate Cell D1 contains a checkbox that will be checked automatically when the current price reaches either the high or low. Allow you to manually deselect the checkbox, resetting until a new high or low is hit next time

[@rubenmunozverdu7528]

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...

[@ExcelCampus]

Great question! Unfortunately, EXPAND returns a #VALUE error when a 0 is specified for rows.

[@redhaakhund1271]

⭐️⭐️⭐️⭐️⭐️

[@rajeshmajumdar4999]

⭐⭐⭐⭐⭐