New Checkboxes was the reason I signed up to Beta last year. I haven't watched the video in it's entirety yet. I paused at #6 (Filter to Compare Rows) especially the advanced part where I can highlight the following row. I downloaded your file so I could check out the complete Conditional Formatting. I found the perfect spreadsheet I'm constantly updating that would be perfect for. So glad it works as planned. 👍
This is fabulous information and SO HELPFUL! Thanks! I would definitely like to see a full video on the Inv Details formula (LET, XMATCH, VSTACK), please! Thanks!
Thanks Thiago! The checkbox actually can be used for a timestamp without a macro. You do have to turn on iterative calculation in the workbook, so it’s a bit of a workaround. We’ll be publishing a short video on the setup next week.
Super cool video!! Remember the "ghost" state of early checkboxes, when we deleted them, that they were still there, if we hovered the mouse over them? That functionality had the advantage of being able to use it for tri state scenarios. On ghost state (still there but faded) they are blanks. So the tri state: blank, true, false. Can be handy for some scenarios like :not arrived yet, arrived, left or maybe, yes, no..etc. Now to get them in the ghost state( blank state) , choose a range, insert, backspace all the way first cell, Ctr+Enter. Their border will be a little bit faded. So, before , deleting them triggered ghost state, now deleting them deletes them completely but backspace and enter wll get them in that sometimes useful "ghost" state. ✌ formula example: =IFS(ISBLANK(range),"Maybe",range=FALSE,"No",1,"Yes")
Thanks for sharing! That's a cool trick!! I'm just wondering if it's a bug that will eventually be fixed/removed. So I'm not sure I would rely on it for long term solutions. I was a fan of the ghost state, but I also saw how it was confusing for users.
@@ExcelCampus You're welcome!! Me too, fan of ghost state (now is better called faded state) and I was so sad they removed it, and then I found the trick with backspace. Will see. I think it's on purpose. Who wants to use it it's there. ✌🙏
Yes please re filter vstack etc . I've been waiting for these check boxes for a while, I koow you could always do them but often they were a nice idea but not worth it if time was constraint and there were moth important things to do. great vidieo.
Hi, great video, all very interesting and useful. It would be nice if you made a video explanation of the part with the Check Box that displays the details by putting the flag on the check box! This would be very nice. Thanks
Jon, I have a friend who wants to catalog lots of data (books, vacation, chores, household goods, etc.). He thinks Access is the way to go but I'm suggesting Excel for him. Could you explain the difference between the two programs and help me help him better understand why Excel is best for his situation? BTW, I always enjoy your videos, they are a big help.
04:35 Show Details on a Table. I can follow the formula until it gets to the "IF" function? you have something going on over in $O$3:$P$3 but give no details about it? It looks like you have something going on in that range then using that as part of the VSTACK/EXPAND in $J$3:$K$3? A further explanation of the full formal would be greatly appreciated. Edit: I downloaded the workbook so I could follow along. Very clever. 😀😍
Thanks Debbie! $O$3:$P$3 contains header for the show details table. $J$3:$K$3 are blank cells used to add padding and move the show details table down to align it with the row where the checkbox is checked. I'll be doing a follow-up video that walks through the entire formula in detail. I believe some of the techniques used in this formula can be useful for other scenarios as well.
Hi Jon 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
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.
I talk about availability and compatibility with Excel versions at 11:14 in the video. We just added a chapter marker to make that section easier to find. I hope that helps. 🙂
Yes, I would love to see the let/expand formula for the invoice details, thanks. Would be great
Yes please do a video on this long formula so I can do this for jobs and what was spent on that job.
Great idea on using this to see expense details for jobs/projects. I'll try to include that example in the follow up video. Thanks Tony! 👍
Do another video on that long formula plz
Thank you, Jon! Great examples! 😊 Do explain in detail how you get tooltip kind of effect when you tick the data, please.
thanks Jon for this very thorough explanation of the uses for checkboxes!
You're welcome! 😀
Excellent thanks , don't have checkboxes yet in my work 365 version, but hopefully soon
New Checkboxes was the reason I signed up to Beta last year. I haven't watched the video in it's entirety yet. I paused at #6 (Filter to Compare Rows) especially the advanced part where I can highlight the following row. I downloaded your file so I could check out the complete Conditional Formatting. I found the perfect spreadsheet I'm constantly updating that would be perfect for. So glad it works as planned. 👍
I've seen it in detail. Spectacular. It's amazing how many doors have been opened. Thanks for contributing so many ideas, Jon.
Glad you liked it! 😀
This was one of yet very useful conditional formatting. thanks
Glad to hear that! 😀
Really would appreciate a video about the formula in the 4:45 example . It's amazing and give a professional touch to the report
This is fabulous information and SO HELPFUL! Thanks! I would definitely like to see a full video on the Inv Details formula (LET, XMATCH, VSTACK), please! Thanks!
Great video Jon!
Wanna learn more, fantastic resource.
Good video! Lots of great ideas. Would love a video on the let function. That was the first time I’ve seen that one.
Thanks Ben! Will do. 🙂
Truely love it...
Excellent gift, Jon. Highly professional, as always. To review calmly and have a good afternoon. Thanks for sharing.
Thanks Ivan! Hope you have a good one too. 🙂
Excellent video. Wish checkbox could be used for timestamp instead of using macro for that.
Thanks Thiago! The checkbox actually can be used for a timestamp without a macro. You do have to turn on iterative calculation in the workbook, so it’s a bit of a workaround. We’ll be publishing a short video on the setup next week.
Jon, This was a great instructional video. Please do a video on creating the detail list. This was very impressive !
Thank you for your feedback! 😀
That is stunning.
Thank you so much for this beautiful video.
Man I could have used this five years ago for sure. This would have made some work so much easier. Great vid man!
Hi Jon, thanks for a great video! I would also like a tutorial on the filter table formula you showed.
Super cool video!!
Remember the "ghost" state of early checkboxes, when we deleted them, that they were still there, if we hovered the mouse over them?
That functionality had the advantage of being able to use it for tri state scenarios. On ghost state (still there but faded) they are blanks.
So the tri state: blank, true, false.
Can be handy for some scenarios like :not arrived yet, arrived, left or maybe, yes, no..etc.
Now to get them in the ghost state( blank state) , choose a range, insert, backspace all the way first cell, Ctr+Enter. Their border will be a little bit faded.
So, before , deleting them triggered ghost state, now deleting them deletes them completely but backspace and enter wll get them in that sometimes useful "ghost" state. ✌
formula example:
=IFS(ISBLANK(range),"Maybe",range=FALSE,"No",1,"Yes")
Thanks for sharing! That's a cool trick!! I'm just wondering if it's a bug that will eventually be fixed/removed. So I'm not sure I would rely on it for long term solutions. I was a fan of the ghost state, but I also saw how it was confusing for users.
@@ExcelCampus You're welcome!! Me too, fan of ghost state (now is better called faded state) and I was so sad they removed it, and then I found the trick with backspace. Will see. I think it's on purpose. Who wants to use it it's there. ✌🙏
Excellent ❤
Congratulations, very good
Yes please re filter vstack etc . I've been waiting for these check boxes for a while, I koow you could always do them but often they were a nice idea but not worth it if time was constraint and there were moth important things to do. great vidieo.
Thanks William! Will do!
thanks a lot
As usual great video! One question: is it possible to add a table row that has a checkbox (365) using an input form?
Hi, great video, all very interesting and useful. It would be nice if you made a video explanation of the part with the Check Box that displays the details by putting the flag on the check box! This would be very nice. Thanks
Yes I will like to learn more!!!
Great Tutorial with almost everything covered. Shall be very usefull for day to day work of inspection. Thanks John, GOD BLESS.
You're welcome! 😀
Great video as usual! Is it possible to protect the sheet and the checkboxes, but still be able to use the checkboxes? If so, how do i do that?
If the checkboxes are not defined as “protected cells” in cell properties then you will be able to use toggle them off and on.
Can you create a tutorial on how you accomplish the show details example at timestamp 5:05?
Jon, I have a friend who wants to catalog lots of data (books, vacation, chores, household goods, etc.). He thinks Access is the way to go but I'm suggesting Excel for him. Could you explain the difference between the two programs and help me help him better understand why Excel is best for his situation? BTW, I always enjoy your videos, they are a big help.
Made me miss using Excel every day😢
like to see that formula to popup sub-table
@stuartcolquhoun-m1s
2 天前
Yes, please share how to set formula for let/filter to expand the details, thanks.
04:35 Show Details on a Table.
I can follow the formula until it gets to the "IF" function?
you have something going on over in $O$3:$P$3 but give no details about it? It looks like you have something going on in that range then using that as part of the VSTACK/EXPAND in $J$3:$K$3?
A further explanation of the full formal would be greatly appreciated.
Edit:
I downloaded the workbook so I could follow along. Very clever. 😀😍
Thanks Debbie! $O$3:$P$3 contains header for the show details table. $J$3:$K$3 are blank cells used to add padding and move the show details table down to align it with the row where the checkbox is checked. I'll be doing a follow-up video that walks through the entire formula in detail. I believe some of the techniques used in this formula can be useful for other scenarios as well.
How do you select one checkbox at a time?
Can i highlight an entire row if my checkbox is not in the first column? Mine is in Column N
Hi Jon 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
yes please, this extra thing you talk about in 5 min 10 sec, please more details. thx
I just found out that you can put formula in a check box, and the check box will be checked if the formula returns true
IF YOU FOUND THEN CAN YOU SHARE THAT IF POSSIBLE. automation (Reverse Checkboxes) with use formula
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.
Sadly, I'm limited to my IT department admins and don't have this the easy way
Show Details on a Table - video please
The checkboxes I've been using for the last month have now disappeared, but Excel says my version has been made up to date, Office 365
What excel version is this available in?
OFFICE 365
I talk about availability and compatibility with Excel versions at 11:14 in the video. We just added a chapter marker to make that section easier to find. I hope that helps. 🙂