I love the graphic with the selectors depending on the activation of the CheckBoxes. Thank you Mynda. As always, a video with a lot of practical information.
Fantastic video as usual… I had never thought that Excel might become the key to the survival of my kid’s Guinea pigs … 😅 I love this solution, and also the other two… amazing Mynda!
I believe one reason why check boxes in excel aren't used frequently is because we have to link each of them to specific cell one by one. But, there is one mind blowing trick with form controls that allows user to overwrite in cell based on multiple cells/inputs (basically dual supervisor cells/inputs on single cell: Step 1: Use have to allow circular referencing on the sheet. Step 2: Add 2 or more form control buttons (I used up-down) and reference them to same cell. Step 3: Now, when you change values using any of the buttons, it will change the value of the cell. Use case: When we want to change the core theme/scenario of an excel model, let's say there are different scenarios like Normal, Good, Bad and calculation are based on the selected scenario, and you want to change the scenario from any sheet of the model, one can make these buttons on each sheet to change values accordingly.
Another great tutorial, Mynda. Thank you. Would you please explain the differences between and different use cases for Form Controls & ActiveX Controls? (Perhaps as a separate tutorial?)
Hi Mynda, very useful (as usual) and very good use of the checkboxes. You can simplify your formula to stripped the color of the rows with =MOD(ROW(),2)
Thanks, Frederic! =MOD(ROW(),2) doesn't allow for filtering the rows. I always use SUBTOTAL to handle this while still ensuring the stripes are correctly alternating, albeit in that example I didn't have filters enabled 😉
Thanks Mynda, you have a solution for everything excel!. Wondering if you can fix the position of the check box's, if I move the chart the check box's stay put?
I'm not sure if you want them to move with the chart or not, but if so, you can select all the objects (check boxes and the chart) > right click > group. Otherwise, if you right click the check boxes > properties, you can set them to move or not move with row/column adjustments.
Another really really helpful tutorial. I really appreciate all your videos and very often can inspire a direction of travel for my dept that I had not thought available so often able to implement more productive way of working. Your videos are a fabulous resource for learning and inspiration. ( bonus is you are also beautiful )
Hi Mynda, Thank you for another fantastic training video; you are certainly my Excel MVP and I appreciate your efforts. Question: in the banded rows topic, why did you not opt for using ROW() minus row of the fixed header to fund the row? Cheers, Mark
Thanks so much, Mark! I didn't use ROW because when you apply filters to the table, ROW cannot detect that rows are filtered and adjust the banding so it still alternates between the visible rows correctly.
Hi Mynda. Thank you. I just tried your example but find it very tedious to assign every checkbox one by one. But Chat GTP was of great help, I asked to write a macro and that worked fine. If you are ready, do not save as XLSM, just save the code for later as text somewhere..., Here is the code: Sub AddCheckboxes() Dim chkBox As CheckBox Dim i As Integer For i = 1 To 20 Set chkBox = ActiveSheet.CheckBoxes.Add(Left:=Range("A" & i).Left, _ Top:=Range("A" & i).Top, Width:=Range("A" & i).Width, Height:=Range("A" & i).Height) With chkBox .LinkedCell = "B" & i .Display3DShading = False .Caption = "" End With Next i End Sub
Thanks for sharing. I agree, if you have a lot of check boxes it's a lot. I prefer to avoid .xlsm file types wherever possible, but sometimes there's no avoiding them.
Hi @MyOnlineTrainingHub, I have the huge of data with checkbox and because of large data I want to put scrollbar but my question is, is it possible to put a scrollbar in order to move up or down the data together with the checkbox?
The AND function returns TRUE when all arguments are TRUE. The second argument in AND is whether the check box is ticked (TRUE) or not (FALSE). There is no logical test as such, it's simply picking up the status of the check box. Hope that clarifies things, but if you're still stuck, feel free to post your question on our forum where we can help you further as I am unlikely to see your reply here due to the volume of comments I get: www.myonlinetraininghub.com/excel-forum
Mynda hi, Very useful tutorial. I thinks it will be better when we can use this tricks by using filter function which is based on TRUE arguments in formula 😉 We can filter all values by clicking check boxes which returns true or false
I've seen some other videos inserting check boxes from insert options in the online Excel. Maybe it was Google sheets not sure. Can they only be accessed via Developer?
Not sure exactly what you mean, but in theory you can link a formula to the output of a text box that displays/hides text e.g. =IF(B2, "Show This Text", "") If that doesn't answer your question, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
should be able to use this formula so it won't matter about blanks in any row, and it should be faster than using subtotal: =AND(MOD(ROW()+ROW($A$4),2)=0,$F$2)
Thanks, however if you filter the table the formula doesn't correctly filter the odd/even rows. You end up with blocks of multiple rows shaded. Try it on my example and filter for Clothing and you'll see what I mean.
Hard to say without seeing your file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I am adding checkboxes for my sheet, but I have over 200 rows and want to use a dynamic formula when a new row is created (new data) How do i go about doing that?
Hi Mynda, when i use =sum(--F5:F12) or sum(F5:F12*1), i got the error as "#VALUE!", but when I tried "=--F5", the result is "1"which is correct. Could you help with it? Thank you
Really Cool !!! especially the Coercion - - and the IF "boosting language"!!! Really nice boost from a Really Successful Mother😇
Thanks so much!
it blew my mind, how you can utilize a small feature and expand it to do great things...super cool applications..
So glad you like it!
I love the graphic with the selectors depending on the activation of the CheckBoxes. Thank you Mynda. As always, a video with a lot of practical information.
Glad you like them, Ivan!
Fantastic Mynda - now all you need to do is teach Diego to use Excel!
Might be easier than teaching my kids 😁
I love using the check box to turn conditional formatting on and off. Great example. Thank you for sharing.
Great minds think alike 😃
Nice video! The old tricks are always the best :)
Yes they are! 😊
Fantastic video as usual… I had never thought that Excel might become the key to the survival of my kid’s Guinea pigs … 😅 I love this solution, and also the other two… amazing Mynda!
😁 glad I could help!
I believe one reason why check boxes in excel aren't used frequently is because we have to link each of them to specific cell one by one. But, there is one mind blowing trick with form controls that allows user to overwrite in cell based on multiple cells/inputs (basically dual supervisor cells/inputs on single cell:
Step 1: Use have to allow circular referencing on the sheet.
Step 2: Add 2 or more form control buttons (I used up-down) and reference them to same cell.
Step 3: Now, when you change values using any of the buttons, it will change the value of the cell.
Use case: When we want to change the core theme/scenario of an excel model, let's say there are different scenarios like Normal, Good, Bad and calculation are based on the selected scenario, and you want to change the scenario from any sheet of the model, one can make these buttons on each sheet to change values accordingly.
Thanks for sharing!
Thanks you, professor Mynda, for new ideas!)
Glad you like them!
I take off my non-Australian hat! Incredible as always!
Thanks so much!
Holy moly! This is awesome! Love the nested IFs to give the different alert text strings! Very cool! Thanks as always Mynda! 🥰🥰
Awesome to hear! Please share it 😊
Thank you so much Lynda, Great video !
Glad you enjoyed it!
Another great tutorial, Mynda. Thank you. Would you please explain the differences between and different use cases for Form Controls & ActiveX Controls? (Perhaps as a separate tutorial?)
Cheers, Jim! ActiveX Controls can be programmed with VBA. They also have more advanced formatting options. Form Controls are the more basic version.
@@MyOnlineTrainingHub Thanks, Mynda. Cheers to you & Phil! Keep up the great work you do!
As usual, a great training video. Also, great to see that a boy has to wear a tie to school.
Thanks for watching! 😊
Hi Mynda, very useful (as usual) and very good use of the checkboxes. You can simplify your formula to stripped the color of the rows with =MOD(ROW(),2)
Thanks, Frederic! =MOD(ROW(),2) doesn't allow for filtering the rows. I always use SUBTOTAL to handle this while still ensuring the stripes are correctly alternating, albeit in that example I didn't have filters enabled 😉
@@MyOnlineTrainingHub I didin't know this subtilty with SUBTOTAL. I will test it now
Thanks Mynda, you have a solution for everything excel!. Wondering if you can fix the position of the check box's, if I move the chart the check box's stay put?
I'm not sure if you want them to move with the chart or not, but if so, you can select all the objects (check boxes and the chart) > right click > group. Otherwise, if you right click the check boxes > properties, you can set them to move or not move with row/column adjustments.
Another really really helpful tutorial. I really appreciate all your videos and very often can inspire a direction of travel for my dept that I had not thought available so often able to implement more productive way of working. Your videos are a fabulous resource for learning and inspiration. ( bonus is you are also beautiful )
Glad you like them! 🙏
I wish I were this little son of yours. By age 10, I'd be a professor of Excel. What an opportunity this son to have a mommy like you😂
Ha, I wish he had some interest in Excel 🙄 it seems he didn't inherit my love for it.
@@MyOnlineTrainingHub He might be elsewhere, probably better than mommy🤗
Very nice video, thabk u for sharing this with us 😊
My pleasure 😊
Very nice Mynda, thank you!
Cheers, Chris!
another great lecture. Thank you very much.
Glad you liked it!
Hi Mynda,
Thank you for another fantastic training video; you are certainly my Excel MVP and I appreciate your efforts.
Question: in the banded rows topic, why did you not opt for using ROW() minus row of the fixed header to fund the row?
Cheers,
Mark
Thanks so much, Mark! I didn't use ROW because when you apply filters to the table, ROW cannot detect that rows are filtered and adjust the banding so it still alternates between the visible rows correctly.
@@MyOnlineTrainingHub Hi Mynda, that makes complete sense! Thank you.
Cheers,
Mark
Thank you for the tutorial. I am challenged by filtering a data with check boxes. The check boxes mix up after filtering
Yes, you can't have the check boxes on rows you're going to filter.
Very interesting topic..... Thank you, Mam...
Most welcome 😊
❤ shared with friends 👍😎✊
Awesome! Thank you 🙏
was so good to watch. super!
Glad you enjoyed it! 🙏
Hi Mynda. Thank you. I just tried your example but find it very tedious to assign every checkbox one by one. But Chat GTP was of great help, I asked to write a macro and that worked fine. If you are ready, do not save as XLSM, just save the code for later as text somewhere..., Here is the code:
Sub AddCheckboxes()
Dim chkBox As CheckBox
Dim i As Integer
For i = 1 To 20
Set chkBox = ActiveSheet.CheckBoxes.Add(Left:=Range("A" & i).Left, _
Top:=Range("A" & i).Top, Width:=Range("A" & i).Width, Height:=Range("A" & i).Height)
With chkBox
.LinkedCell = "B" & i
.Display3DShading = False
.Caption = ""
End With
Next i
End Sub
Thanks for sharing. I agree, if you have a lot of check boxes it's a lot. I prefer to avoid .xlsm file types wherever possible, but sometimes there's no avoiding them.
@@MyOnlineTrainingHub sure, after you are done, just delete the code and save as xlsx
Nice one. Keep it up!
Thanks, will do!
Awesome 🥰
Glad you liked it!
Hi @MyOnlineTrainingHub, I have the huge of data with checkbox and because of large data I want to put scrollbar but my question is, is it possible to put a scrollbar in order to move up or down the data together with the checkbox?
Not with these type of checkboxes, but with the new ones you can: th-cam.com/video/MB9DWvHXQLk/w-d-xo.html
GREAT !!! Thanks
Glad you liked it!
Thank you ma'am 😊
My pleasure!
Please explain the second part of the AND function. I would expect a formula to evaluate the cell, but thou only enters $F$2.
The AND function returns TRUE when all arguments are TRUE. The second argument in AND is whether the check box is ticked (TRUE) or not (FALSE). There is no logical test as such, it's simply picking up the status of the check box. Hope that clarifies things, but if you're still stuck, feel free to post your question on our forum where we can help you further as I am unlikely to see your reply here due to the volume of comments I get: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub this is clear now. thanks.
I LOVE IT...!!😀
Awesome to hear 🙏
Mynda hi,
Very useful tutorial.
I thinks it will be better when we can use this tricks by using filter function which is based on TRUE arguments in formula 😉
We can filter all values by clicking check boxes which returns true or false
Yes, great idea!
Thank you ❤
You're welcome 😊
I've seen some other videos inserting check boxes from insert options in the online Excel. Maybe it was Google sheets not sure. Can they only be accessed via Developer?
Yes, only via the developer tab in Excel.
Never heard of that double-unary trick.
Glad I could share something new 😊
What version of excel will this work on (Excel desktop 2021?)
Yes, it works on all desktop versions.
Is there away to make the check boxes produce text, so it produces the same layout text everytime the checklist is ticked?
This something that would help me a lot at work but can’t find anything online. Essentially an “alert generator” or “text generator”
Not sure exactly what you mean, but in theory you can link a formula to the output of a text box that displays/hides text e.g. =IF(B2, "Show This Text", "")
If that doesn't answer your question, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub I’ll give that a try! Thank you
should be able to use this formula so it won't matter about blanks in any row, and it should be faster than using subtotal: =AND(MOD(ROW()+ROW($A$4),2)=0,$F$2)
Thanks, however if you filter the table the formula doesn't correctly filter the odd/even rows. You end up with blocks of multiple rows shaded. Try it on my example and filter for Clothing and you'll see what I mean.
hi, i have a problem the check box does not toggle , what i'm doing wrong (the "mixed" opt at the control dialog box is grey) ? thanks
Hard to say without seeing your file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I am adding checkboxes for my sheet, but I have over 200 rows and want to use a dynamic formula when a new row is created (new data) How do i go about doing that?
Try using the new checkboxes instead: th-cam.com/video/MB9DWvHXQLk/w-d-xo.html
Hi Mynda, when i use =sum(--F5:F12) or sum(F5:F12*1), i got the error as "#VALUE!", but when I tried "=--F5", the result is "1"which is correct. Could you help with it? Thank you
I suspect there are non numeric values somewhere in the range F6:F12.
💚
Glad you liked it! 🙏
thanks for these tutorials, very helpful. plus thanks for being so hot.
Glad you like them!
😎👍🤜💥
💯
❤
Glad you liked it!