Earned a subscriber. I have always used drop-down lists, then after frustrations with constantly having to update it after new additions I learned that you can reference the Table column instead and it would capture everything, but I found that it STILL didn't update for new entries. This video finally taught me that you have to use the Name feature within data validation to truly capture the entire column including future additions. Thanks!
Great question! Microsoft recently released an update for Excel that allows you to search dropdown lists. Here is a video that explains more about it. ua-cam.com/video/_GWHFT3rEIA/v-deo.html If you don't have the latest version of Excel, then I've created a free add-in called List Search that allows you to search the dropdown lists. Here is a video on List Search. ua-cam.com/video/kntHn2J7pdk/v-deo.html I hope that helps. Thanks again and have a nice day! 🙂
Thanks for sharing! I have a question.. When I have a drop down list for my users to use during work, is there a way to have it set up in a way that if two users are on it at the same time, they cannot see what the other is doing or looking for?
You sold me on tables....I used the append feature you highlighted on another video to squish together multiple data source files together into a master list + remove duplicates since the output file rank orders the first row of a client - the remainders were removed. XD thanks Jon!!
What you forgot to mention: users can overwrite DD validation check by simply pasting stuff into the cell. Excel is bad for the purpose of user input to track things. If you have to stick with it, add some basic VBA code doing the input validation (Worksheet.Change event listener) so it cannot be overwritten and ensures data integrity + you should overwrite the default paste-behavior to value only to avoid format and formula madness when multiple peeps using it.
Can be possible client wise auto update loan amotozation table? Also if possible interest rate change so auto update automatic in excel Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment already showing in your video ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest
Great vid as always. If I was using a vlookup/xlookup and referencing the original category list, my table array would be "Category" in cell A1 of the "category list" tab? I wouldn't need to define the range, as you said this step is specific to drop down lists, correct ? Trying to find the video where you explain tables this way!
Hi I must congratulate you on your awesome video. However I am struggling with one thing, blank cells in the drop downs. How do I remove them? Please can anyone help?
Earned a subscriber.
I have always used drop-down lists, then after frustrations with constantly having to update it after new additions I learned that you can reference the Table column instead and it would capture everything, but I found that it STILL didn't update for new entries.
This video finally taught me that you have to use the Name feature within data validation to truly capture the entire column including future additions. Thanks!
So glad we could help you learn something new! And thanks for the subscribe!
Can this be taken one step further e.g. make searchable?
Great question! Microsoft recently released an update for Excel that allows you to search dropdown lists. Here is a video that explains more about it. ua-cam.com/video/_GWHFT3rEIA/v-deo.html
If you don't have the latest version of Excel, then I've created a free add-in called List Search that allows you to search the dropdown lists. Here is a video on List Search. ua-cam.com/video/kntHn2J7pdk/v-deo.html
I hope that helps. Thanks again and have a nice day! 🙂
A great explanation Jon! Thank you for telling it in such a didactic way.
Thanks for sharing! I have a question.. When I have a drop down list for my users to use during work, is there a way to have it set up in a way that if two users are on it at the same time, they cannot see what the other is doing or looking for?
Hi boss, I have a challenger in excel, But I can't accomplished, can you help me, if you can?
Great video Jon! Simple to understand example with a good voiceover 👍🏻
Thanks Mahendra! 👍
What about sorting the table automatically for new entries?
Thank you for sharing- very helpful.
You sold me on tables....I used the append feature you highlighted on another video to squish together multiple data source files together into a master list + remove duplicates since the output file rank orders the first row of a client - the remainders were removed. XD thanks Jon!!
You're welcome, William! :)
Great tip Jon, will help me allot going forward
Excellent. Thank you
Thank you Honey!! Have a lovely weekend!!xx
Thank u Jon for this great video. By the way, the office / room where u r filming 🎥 this video seems very nice.
Thanks Nader! It's my home office and my sanctuary. The rest of the house is for the kids (chaos) 😂
What you forgot to mention: users can overwrite DD validation check by simply pasting stuff into the cell.
Excel is bad for the purpose of user input to track things. If you have to stick with it, add some basic VBA code doing the input validation (Worksheet.Change event listener) so it cannot be overwritten and ensures data integrity + you should overwrite the default paste-behavior to value only to avoid format and formula madness when multiple peeps using it.
Great Jon.. thanks!!
Great advice once again, thanks Jon
Can be possible client wise auto update loan amotozation table?
Also if possible interest rate change so auto update automatic in excel
Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment already showing in your video ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest
Nice one 👍
Great vid as always. If I was using a vlookup/xlookup and referencing the original category list, my table array would be "Category" in cell A1 of the "category list" tab? I wouldn't need to define the range, as you said this step is specific to drop down lists, correct ?
Trying to find the video where you explain tables this way!
Excellent 👍
Really Great Tips...Thank You Jon :)
Thanks Darryl! 🙌
So helpful, thank you Jon, it was very clear and concise
Thanks Malika! 😊
Hi I must congratulate you on your awesome video. However I am struggling with one thing, blank cells in the drop downs. How do I remove them? Please can anyone help?
So helpful, thank you ! You explain really well and also cover parts well that we would wanna know
Thanks Nisha! I appreciate the nice feedback. 😊
Hi Jon, why we need to define name range, the drop down list is working when we add new category in the table without defining name of the range?