Learn Excel online https://www.udemy.com/excel-2010-superhero-course/?couponCode=Excel2010YouTube Create dependent drop down lists in Excel to select items based on another list selection. Create lists that are dependent on other lists to limit options and help validate entries. VISIT COMPUTERGAGA FOR LOTS MORE TIPS http://www.computergaga.com CONNECT WITH US! Google+ ► https://plus.google.com/+Computergaga Facebook ► http://www.facebook.com/computergaga Twitter ► http://www.twitter.com/computergaga1
Go to http://www.contextures.com/xlDataVal02.html to get the sample workbook Select an item from an Excel drop down list, and see only the related items in the next drop down list. In this example, select Fruit from the first drop down, and only fruit names appear in the next column's drop down list. Instructor: Debra Dalgleish, Contextures Inc. Get Debra's weekly Excel tips: http://www.contextures.com/signup01 More Excel Tips and Tutorials: http://www.contextures.com/tiptech.html Video Timeline: 00:00 Intro 00:39 Set Up the Worksheets 01:15 One-Word Names 01:41 Create Tables 02:55 Name the List Items 04:15 Main Drop Down List 05:48 Dependent Drop Down Lists 07:39 Get the Sample File Video Transcript (abridged) In this video, you'll see how to create two dropdown lists. The first one, for produce type, fruit or vegetable. In the next column, there's a dependent dropdown list, it will change based on what you selected in the other column. Here it shows a list of vegetables. If I go to next row and select fruit, Column C shows list of fruit. Here's a new workbook where we're going to set up dropdown lists. We've got 2 sheets. One is Data Entry, other one is called Lists. Start a new blank workbook and name the 2 sheets. I typed Produce Type and Item on this sheet, where we're going to create dropdown lists. On the other sheet, I've got 3 lists set up, Produce List with Fruit and Vegetable, and a fruit list, and vegetable list, with 4 vegetables. The produce list is our main list. We have 2 items, and it's important to use a one-word name in this list for each produce type. We'll those names later, and Excel won't allow spaces. Each list will be changed to a formatted Excel table. In the first one, click anywhere in the list. On the Home tab, go to Format as Table, select a format that you like. My list is B2 to B4, and has a header. Click OK, and there's our formatted list. It has a dropdown list in the header cell, to filter or sort, and a corner tab here to change the size of this table. If we add new items, the table automatically expands to include them. We'll do the same thing for the fruit and vegetable list. Click anywhere in this list, Format as Table. Select a color. Again, it has headers. And same for vegetable, green, has header. The next step is name cells with items in each list. Select cells B3 and B4, with produce type names, and to name these 2 cells, click the Name Box. Type a one-word name, Produce, then press Enter, to complete the name. It still shows Table1, but click the arrow to see Produce. Give the fruit list a name, it has to be an exact match for the item in this produce list. Select these 4 cells, click in Name Box, type fruit, press Enter. Upper or lower case doesn't matter, as long as spelling is the same. Now we have a fruit list, do the same here, this will be called Vegetable, press enter. Now we can see all 3 names, Fruit, Produce, and Vegetable, and table names. Next, set up the data entry sheet. I've got headings entered, and I'll create a table from these 2 cells, then add items in rows below. With a heading cell selected, on Home tab, Format as Table, select style you like. Again, my table has headers, OK. It creates a table with one blank row below headings. We'll set up a very simple dropdown in this cell, where we can select a produce types, fruit or vegetable. Go to Data tab, click data validation, on Settings tab, select List. For source, we'll use our produce list. On a Windows computer, press F3 key, to see names you've created. Click Produce, OK, it puts in an = and the name of that range. If you don't have that shortcut, type =produce, click OK. Now we've got a dropdown arrow when this cell is selected, select fruit or vegetable. In this cell, we'll create dropdown that depends on what we selected. If Fruit is selected, it will show fruit list, if we chose Vegetable, we'll only see vegetables. To create this dropdown, on Data tab, click Data Validation. We want a list, and this time, we'll use a formula. Type =. The function we're going to use in this formula is INDIRECT, then click an open bracket. We want to use whatever name has been selected, so type B3, close that bracket. Instead of typing fruit or vegetable, we'll use INDIRECT function to get name that we want to use from cell B3, and create a reference to that list, click OK. It will create a list based on that produce type. Select lemon. Go to next row and type fruit again, it shows fruit list. Change to vegetable, it shows list of vegetables. So this is dependent dropdown list. We create a main dropdown, then a dependent dropdown- items in that list depend on cell we told it to look at. Go to http://www.contextures.com/xlDataVal02.html to get the sample workbook
Learn how to dynamically change selections in one drop-down list based on value selected from another drop-down list. example of that could be selecting department in the first drop-down list and the second drop-down list immediately start showing only employees in that department. The way we are doing it, adding new items to the master list make the new item available immediately in the drop-down menu selection without having to change the range inside the name manager! it is a big time saver! Hope you Like it!
Check out my Blog: http://exceltraining101.blogspot.com How to create a dynamic drop down list in Excel. #exceltips #exceltipsandtricks #exceltutorial #doughexcel
http://5minuteguide.net This lesson will explain how to use the INDIRECT function and the VLOOKUP function to create two dependent drop down lists that have different information, but that both change based on a master drop down list in Microsoft Excel. This video was created using Screenflow by Telestream - https://store.telestream.net/order/product.php?PRODS=4578129&QTY=1&AFFILIATE=54365 Forum for Viraj's question: http://www.excelforum.com/excel-formulas-and-functions/937664-massive-data-validation-with-dropdown-lists.html?p=3314892#post3314892 Multiple selections with VBA & Data Validation: http://blog.contextures.com/archives/2011/04/29/how-to-set-up-multiple-selection-excel-drop-down/ Amazon Affiliate link to Excel 2016 For Dummies (Excel for Dummies) https://www.amazon.com/gp/product/111929343X/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=111929343X&linkCode=as2&tag=indelible01-20&linkId=23a5da3d4a16e3423d809a92e09a5fbe
In this video, I show you how to insert a drop-down box in an order form in Excel and use VLookup to auto-fill the contact info fields. This is a great way to select a customer number and have all of the address fields auto-fill. It's pretty simple once you get the hang of it! Don't forget to subscribe to my channel for more tutorials! Check out my website at http://www.sallyu.com Video recorded using Screencast-O-Matic (http://www.screencast-o-matic.com) Thanks for tuning in!
Learn these ten Excel Paste Special tricks to make you a pro. Pasting data is one of the most common actions, but do you know these Paste Special tricks to save time and perform secret operations. These tricks include different examples of using the paste operations and useful 'real world' examples of using Paste Special. Download '27 Awesome Excel Hacks to Make you a Superstar' - http://www.computergaga.com/27-excel-hacks-to-make-you-a-superstar.html Find more great free tutorials at; http://www.computergaga.com Connect with us! Google+ ► https://plus.google.com/+Computergaga Facebook ► http://www.facebook.com/computergaga Twitter ► http://www.twitter.com/computergaga1
In this video we look at 2 awesome Excel formula shortcuts that I like to use. Want to be better at Excel formulas? Enrol in the online course - https://www.udemy.com/excel-formulas-made-easy/?couponCode=LIVE10 In the video we show a shortcuts that will take you from the formula to a highlighted range quickly for editing. We also see a shortcut to help understand how a formula works and where it may be going wrong. **FREE Instant download - 27 Awesome Excel Hacks - https://goo.gl/02ebSu Find more great free tutorials at; http://www.computergaga.com Connect with us! Google+ ► https://plus.google.com/+Computergaga Facebook ► http://www.facebook.com/computergaga Twitter ► http://www.twitter.com/computergaga1
Create an interactive checklist in Excel that automatically marks items when they are completed. FREE Instant download - 27 Awesome Excel Hacks - https://goo.gl/02ebSu Learn Excel VBA from Scratch FAST - http://goo.gl/oe72Rj In this video tutorial you will see how to insert checkboxes in Excel, link them to cells and then automatically format them when completed. Watch this video to learn how to create a macro that will set the link cell for all of the checkboxes on a worksheet - https://youtu.be/c69wMu7g1UQ The video will make you understand how checkboxes in Excel work. And how Conditional Formatting can be applied to the value of a checked box. Find more great free tutorials at; http://www.computergaga.com Connect with us! Google+ ► https://plus.google.com/+Computergaga Facebook ► http://www.facebook.com/computergaga Twitter ► http://www.twitter.com/computergaga1
I created this video lesson in response to a viewer request. She knew that there was a function that Excel uses to join two "Pick-From" lists in Data Validation. She just could not remember the Function - the =INDIRECT() function - nor could she remember how to do it. Perhaps you have the same question. Or a different question. Simply write to me and I will try to solve your problem with a new video lessons. I wish all of my viewers a happy new year in 2009! Danny Rocks