I created a very complex spreadsheet that accomplished exactly what you explained in the video, but your version was much more effective, less time-consuming, reduces formulas and thus file size. When I get back to work, I will begin to incorporate what I have learned here. THANK YOU SO VERY MUCH.
This the easiest and fastest tutorial on dependent drop down without blanks. Thank you for this video! ☺️ Liked, and subscribed. Even added the alarm for new videos.
Dear sir, i watch many excel videos for years, today by chance i found your video which is unique navigation list, where i can look into menu of items to choose. it very simple but dynamic for my job. Keep up great work, i shall subscribe to learn more from you.Great Job!
Still valid in 2019, thank you! If you want to use space or other letter in the main drop down list, you can replace them in the indirect selection: =INDIREKT(BYT.UT(BYT.UT(B10;" ";"");"+";"")) (adapt to your language INDIRECT and SUBSTITUTE) This removes any spaces and + in table header, but still visible in drop down list. Remove the same letters in the name definition.
Thank you, this is superb. I was able to follow along even though I have a much older version of Excel on a Mac. This is the best explanation I have found.
I followed some other youtube videos on this subject but this one is the best. This is an excellent solution. I am impressed. liked and new subscriber to you channel.
wow thanks for the great feedback, it means a lot and give me a boost to do more, in fact I have improved this method a bit, keep an eye on the channel there will be a new video soon about this subject. thanks again :)
Very helpful video. At 13:33, what we have to do so if food type is set to "Vegetables", "Broccoli" will not appear because it was selected earlier ? Thanks
If you have an issue with the indirect function, make sure the reference name (top row) doesn't have any spaces in between characters (ex. "Project Management" switch to >>> "Project_Management" or "PM"). Hope this helps.
Thanks for your feedback and you’re right, having no spaces is a best practice and makes things easier when creating lists, however, you can have spaces if you must but you need to also include SUBSTITUTE function to handle spaces.
@@ELKAMELBI I don't believe, Substitute can handle ALL of the invalid characters. E.g.: : ; < = > ? @ ¢ £ ¥ { | } ~ « » ! How do you handle these all? Not to mention, that Excel does it differnetly in the name and at the end of the name...
You're most welcome, and thanks for taking the time to put this comment, it really helps me to keep going, I am glad that the tutorial was easy to understand, I tried my best to make it clear for everyone, glad to see it worked :)
I have no clue what I'm doing wrong, but I keep getting the error of "The Source currently evaluates to an error. Do you want to continue?" when I'm at the =Indirect() step of your video. Around 12:45.
Just a quick check, does your column headers have space in them? That might be the reason, if that’s the case, remove spaces from headers and try again. If you want to keep spaces in headers you need to use SUBSTITUTE function, I can help with if you send me the file by email.
@@ELKAMELBI Thank you for the quick reply! I removed all spaces from headers and table names and no luck. The project I'm working on will by quite tricky, but I was trying to do a test run with one section of my project. I'd be happy to send you an example, where might I find your email?
Great tutorial! I have tried using this with numbers but cant seem to get it to work. I keep getting an error (the source currently evaluates to an error). Is this because the name manager does not allow for #s to be used?
Thanks Dean, yes the name manager prevent you from naming any list by a number alone, the name in name manager has to start with either an underscore (_) or a letter, so to get it to work with numbers you need to add underscore to your names in name manager and when using to refer them prefix the cell that contain the number with "_"&. so if cell A1 contains the number that you've used as a name (prefixed by _), you can call the list by writing =SUM(indirect("_"&A1)). does that answer your question, if not send me a sample file at abdallah.elkamel@gmail(dot)com and I'll see what I can do. good luck...
i enjoyed your tutorial, *abdallah* … 'specially with regard to the _"indirect"_ function. you explained yourself almost exactly the way i explain myself … when instructing someone.
in the next few weeks … will try implementing _"dependent drop-down lists"_ within *libre-office* or *numbers* _(mac-os)_ … who knows, i might come away with a bit of luck.
Thanks so much Pnamajck for the kind words, I am glad you liked the video, Working on a video now to expand on this skill, the new way of dependent drop down list fixed the issue of having spaces in the column header and also a new list can be added automatically, keep an eye on the channel in the coming days, thanks again.
Hi, thanks for this tutorial, really useful... now, Is it possible to do the same drop-down list but instead of showing the value, show some text, for example, using a table of products and prices, and when choosing the product name, the value on the cell changes to the respective price, is it possible?
Yes, you use VLOOKUP function to get the price from the product/price table based on the product you select by a drop down list. so the first cell will be a drop down list that lists the products, once you select a product the cell next to it will take the product name you just selected and use VLOOKUP to get the price from the products/price table. I have a video on VLOOKUP on the channel you can check it out.
@@ELKAMELBI Could you please help me further? --I entered "fruits" then selected "apples" first, then I changed the "Fruits" to "Vegetables", but "apples" still remains in the cell and i had to manually delete it. Is there a way to let it automatically go away since "Apples" doesn't belong to the new Food Type i selected? If i don't delete it, Data Validation didn't pop out any error message either. Thank you!
@@linm8649 for that to work you need to use VBA code to clear the cell after change happens in fruit/vegetable cell but data validation on its own cannot do that. managing lists in Excel is still weak to answer business questions specially when you start having dependent drop-down lists. if you look it up in google you will get a sample code that you can apply, I usually avoid using VBA as much as I can.
Many thanks Mr. Abdallah ! I have been looking for this one. How can I make prices automatically read from (tblPrices)? so we need only to enter the quantity manually.
Hi Mohammed, which video tutorial are you referring to? this video doesn't have tblPrices. are you referring to another video? thanks. to answer your question in general, VLOOKUP function is normally used to do this but give me more details, also you can contact me at abdallah.elkamel(at)gmail(dot)com
Was the vba code? Or do you mean formulas ? If vba code then yes because you can link it to a form. I don't vba code and I am not a fan of it. However, if it is a formulas that enables you to make multiple selections in a cell, I would like to see that, I am yet to see something like this without being overly complicated and involves redundant data.
Thanks for this i have a similar case but on gym thing not to make it complex i will set the question per your context suppose for this case you need to quantity to populate automaticaly according to the chosen fruit type how can i do it i assume each fruit corresponds to a food type,
i mean each fruit type corresponds to a quantity how can i have excel populate the quantity i am using v look up but when i reference it to the second list i get an error
Thanks for your comment, the best way is to use VLOOKUP function to do that providing that fruit type names are unique, let me know if you need help setting that up.
+Data Secrets Learning the problem is that i tried the vlook up a million times but it keeps throwing an error #NA, when i reference it to The depedent list(Fruit list for this case)
It is great, i have tried the food type list more than two (as you have presented in the video is only two) but could not able to make drop down list with the formula that you have used which is indirect........ so please help me ..
@@nabarajpokhrel2409 send me your file at abdallah.elkamel(at)gmail(dot)com and I will get it fixed for you. we need to use SUBSTITUTE function to replace every space with an _ underscore because if you automatically name a range that contain a space, excel will replace every space in the name with _ underscore
Hi. I am trying to create a drop-down menu which gives me an numerical value. For instance, I have 9 options I have for drop-down choice which have certain numerical value. How can I choose these as a question and when the answer is chosen, the number value populates in the cell next to it? Thanks
To achieve this you need a combination of a dropdown menu on one cell and VLOOKUP on the next cell where you the number to appear. the VLOOKUP function should navigate to a table that has 2 columns, the first one is the source to the dropdown menu and the second column contains the numbers that you want to show in the next cell after you make the selection from the dropdown menu, does that make sense? it is easier shown than explained in writing, so check this file I have just for your issue, a download button should be available at the top of the page, this is the link: 1drv.ms/x/s!AvVs0CO1qJZZqx2XZezbpAINxzsb
Yes, I have a fully automated template that you can use over and over again. here a link to the video where you can find a link in the description to download the file ua-cam.com/video/k8p6sxRgfic/v-deo.html
This video was very helpful for me to learn the dependent drop down list, but one thing I want to learn is if i hve to bring automatically the price of the food in the next row then what formula i will have to use to get that. I will be glad if you answer to my question..waiting
Your video was really helpful but I have a problem cuz my first record of the first column of the table needs to be validation. How will the table expand automatically in this case?
Thanks, yes this is the one case where the table will not expand automatically. you either drag the bottom right corner of the table to add more rows, or use TAB. I prefer using TAB.
Data Secrets Learning Thank you for your answer! :) So if I fill the last cell of the table (for example the Qty entry in the video) and press TAB, it will automatically generate a new record below in the table?
Have a question, if my data has more cells populated how would this sample would be relevant. Example: sheet 1 will be where the data will be presented, sheet 2 has aluminum whish has 4 cells populated as following cell one product code, cell two product name cell 3 description and cell 4 product weight (this cells will be multiplied by product cost (cell 5 on sheet1) then multiplied by length (cell 6 on sheet1) them multyplied by (cell 7 on sheet1) company profit.
thanks for the question Robert, you need to create a drop down list only for product code and it should be created in sheet 1 where you present your data, once a product code is selected in sheet 1 you use VLOOKUP to bring the relevant data for the product code selected like Product Name, Description, weight,...etc. once these are brought in sheet 1 you can then you can perform your calculations on it. it should be fairly easy to setup. However, If you want your file to be setup professionally and maybe add more bells and whistles, I can do that for you for a small fee, let me know if that interest you, meanwhile feel free to ask me more questions if you have or clarifications on my answer above, thanks and good luck.
Thank you Dependent drop down list video. Would it be possible if you could make video about how to create pivot table in excel in android tablets. And also another video about opening 2 excel files at the same time in android tablets. Thank you
I've completed all of the steps but get an error message when I get to the data validation that has =Indirect(C3). I shows a drop down but no content. Can anyone help me understand why that is? The error is not described at all, it just comes up when I try to close the data validation window. Thanks
Hi Diana, not sure why you’re getting this error, you can send me the file you worked on or I send you my file. Once I see your file I can figure out what’s wrong. My email can be found in the channel about section.
Hi, Trust are are doing well!! What To do If Table name is not showing in the Excel sheet. I am trying to Make a sheet but unble to change the name of table because of its non availablity.
SOS !!! I have sheet with thousands of data. NAME as header in 1st Column , COMPANY as header in 2nd column and so on.. all the column have multiple repeated data. So if I select any NAME it should only show the COMPANY where he has worked. please help
Thanks, glad you found it useful. changing color of a cell depending on its value is done by utilizing what is known as "Conditional Formatting". if you haven't used that before, click first on the cell where you want to apply the conditional colors, click on the Home tab and then go to the right side of the screen and you should find the icon, clicking on it will give you options to choose from, select "Highlight Cells Rules"and from the submenu select "Text that contain...", enter the text (in your example, "Yes" or "No") and choose the color you'd like to use when that happens. remember, you need to do this process twice to define the two colors. let me know if you still have issues. there are abundance of conditional formatting videos on UA-cam that you can check include some in this channel. Thanks for your comment and glad you got here.
Your video was extremely helpful, but somehow I'm stuck. When I try tp create a new row by either pulling down, doing "paste special" or by just typing in the empty row below and having, it still only uses the date from the first row with the main drop down list for example C3. The cells in column D will only show the dropdown for the first cell in column C. If anyone has a solution I would be grateful. Thank you!
Thank you so much for the tutorial. It was a tremendous help. I encountered a problem where only some of my 'Foods' pulled through when selecting certain 'Food Types' and others worked perfectly. Where do you suppose the problem occured?
You're welcome Adri, glad you found it useful. now normally this happens if the named range is not covering all Foods, to fix that you need to extend the range to cover the missing items. now in the tutorial I reference column name since we are using formatted tables and that take care of it automatically. if you still facing issues you can send me your file at abdallah.elkamel(at)gmail(dot)com and I will do my best to help.
There is a barely visible/very tiny text or number before you enter tblveges/ or tblfruit in the refers to box under new name. Please, Sir, what is that? Is it an asterisk, a tilde, an = , an arrow...... Located around 8:54, 8:53, 8:52.…. Sorry, disregard. It's an = sign
Hi, I like your video and am trying to follow it but the name table function doesn’t come up like you demonstrated. And, I cant find name manager under Table Tools. Wondering what version you are using and what I am possibly doing wrong. I’m using office365 on onedrive. Thanks.
Thanks Dianne, I believe I was using 2013 version, but there should be no changes between 2013 and 365 which I am also using currently. Name manager should be under formula tab not table tools, and if your table header has spaces you need to throw SUBSTITUTE function in the mix. If you have a business need, I can setup your file for a small fee, I can be reached at abdallah.elkamel(at)gmail(dot)com, can also be reached on skype at abdallah.elkamel
Hi .Thanks for the video. It really helped me a lot. I have s small doubt. Will this not work if we use a space in between the table headers and drop downs?
You can but you will need to use substitute function to make it work, I think I helped someone with that in the comments of this video, if you still have issues you can either describe it here or you can send me an email at abdallah.elkamel(@)gmail(dot)com
Thank you for posting this video. Taking your example, after I select a particular fruit (Banana) value in column D (one of the fruits from dropdown list), I go back and change the value in column C from Fruits to Vegetables, my selection in column D is still showing Banana. But I would expect the cell value becomes null after I change in selection in column C. Can you please suggest someway how to handle this?
that is a common problem, it can be fixed by using vba code using AfterUpdate statement, i am not too fond of vba and i don't use it, if you want a fix for this look online for "clean a cell after update using vba" and most likely you will arrive at the right solution.
مجهود جبار ما شاء الله. اخي،ارجو المساعدة، انشأت ملف كامل مشابه لهذه الفكرة. لبيانات خدمات عملاء وبالاخير الملف غير عملي بسبب كثرة الخدمات في القائمة الاساسية وصغر الخط وعدم وضوحها. هو جدول كبير وفيه قائمتين تتعلق ببعضهم البعض. ارجو الحل. كل الشكر لك يا اخي
Thank you , I was wondering is it ok to creat this in a workbook that has 150 sheets and each one will have like 5 dependant dropndown list , couse I don't want to do all of it and ends up having a problem. Another thing is that , is it ok to move or copy sheet that has defined name and still working the same way ? Couse I tried to do that but Excel said we cannot do this in a sheet that has tables. Thank you
You’re welcome Mohamed, I don’t know the maximum number of named ranges that Excel can handle, but I would question why you had to have 150 sheets in one workbook, there might be a better option like moving to an Access database or another platform. As for the other question, there shouldn’t be a problem copying or moving a sheet that contains tables or named ranges even if you copy to a new sheet, but you can’t have duplicate names, tables and named ranges need to have unique names. Hope that answers your question.
Thank you for the tutorial, it has help me understand Data Validations on a whole new level. With that being said, i'm having trouble with something. So I highlight all my headers and give it a name(headers). I confirm that the headers come up in the Name Mgr (Department, Equip, Brand, Model). I have also created tables for each of these headers with information in their respective columns. The problem comes when on a separate sheet, I create the "form" and label it Department, Equip Type, Brand, Model Serial Number. I format all this as a table then I click on the cell under Department. Following your instructions, I go to Data Validation, select List, the in the Source field i enter =headers. When I click on the drop-down box, it shows me the other column headers rather than the departments I had created tables for. Any help is appreciated.
You're welcome, glad you've enjoyed it. regarding your question, it is hard for me to visualize what is happening with your file, if you send me your file at abdallah.elkamel@gmail.com I will be able to give you a comprehensive answer, thanks for the sub :)
good....i have one question for this. if i choose "Fruits" from fist drop down and in dependent menu i select "Apples". What if i goes to first drop down list and change to "Vegetables" it still stays "Apples" there. I want some error in the "Apples" drop down as i not changed to "Fruits to Vegetables" , how to achieve this error by color or popup?
Hey Rahul, it won’t give you an error by default, it may show green triangle at the edge of the cell to indicate ab error but that showed many times to be unreliable way to check errors. To achieve what you described, you use VBA, you should use AfterUpdate clause applied to Food Type, the code should clear the Food cell whenever Food Type cell is updated. Personally, I am not a fan of VBA, I prefer using Excel old school, but unfortunately there is no way that I can think of right now, maybe conditional formatting with a formula can achieve that! I will keep that in mind and if I run into a solution I will let you know.
@@gajanangalgale205 that is weird, it shouldn't be, 2016 should support having the list in another sheet. maybe the reason is related to something else.
Do you mean having three or four dependent lists? It can get complicated, because you need to, for example, expand all fruits to horizontal tables as you have done with the first list, you would have a table called apple that contains all types of apples and so on. I wouldn’t recommend more than two dependent lists unless it is absolutely necessary.
This is perfect, but how would I do this with more than 1 set of subgroups? For example I'm trying to have the first box give me the option to choose area, then the second box give me a list of equipment types in that area, then the third box give me a list of all the equipment that is listed as that equipment type. Further Example: Box A(area) - Cold End Box B(equipment type) - Robots Box C(specific machine) Robot #3 OR Box A(area) - Ware House Box B(Equipment Type) - Conv. Box C(Specific Machine) - Conv. #3 So when I select a certain option in Box A it gives me a certain range of options in Box B that wouldn't be there if I chose a different option in Box A. And when I choose a certain option in Box B(that correlates with Box A) it gives me a selection of options in Box C that would be different if I chose a different option in Box B. If I chose: Box A(area) - Cold End Box B(equipment type) - the options for warehouse aren't there. Only the ones for Cold End are. (Cold End, Warehouse, Robot, Conveyor are just examples.) How would I make this happen? Thank you.
Thanks for your question, the way to do it is to create a new set of lists with headers named exactly as those in Box B, under each list header put all those options from Box C that correlates with the header of that list, then follow the same data validation procedure described in the video. If these instructions are not clear, I can create a file for you that you can populate with your data later on, you can even send me a sample of data that I can work on :).
sorry for the late reply, your comment for some reason went to Spam, just found it about it. is your question related to the video or it is a general question? i am afraid I don't understand what you're after.
@@Berghiker can you explain what you mean by "if the integer begins with a negative sign", does this mean your negative sign is on the right side? it maybe better if you send me an email at abdallah.elkamel(at)gmail(dot)com, maybe include an example. thanks
hi. thanks for the video. i have question. how to add price automatically when are selecting the drop down item? i mean when you were selecting food. automatically adding price
You're welcome Aldi, glad you liked the video. to make price appear automatically you need to use VLOOKUP function but before that you need to create a list of all your products and their prices, what VLOOKUP does is take the selected item from your drop down list and then go to the price list search for the item in the leftmost column and then pick the corresponding price. if you'd like me to help you send your file to abdallah.elkamel(at)gmail(dot)com.
Doable, but a bit harder to explain and requires a bit of management as you add more categories, basic idea is that the items in the second list will become a header in tree of third list. If this is something you need, send me your file and I will see what I can do.
Hi, great video, thank you for making this. I have it working almost perfectly, except when i pull down the second menu, the name of the first menu is still on top of the dropdown, so like if i chose Fruits, then the second dropdown first option is Fruits, then all of the fruits. any idea what i am doing wrong? I don't want "Fruits" to display on the second dropdown. thanks!
apparently you have included the header cell when you selected the cells for the second list. to fix this go to Formulas tool bar > Name Manager > find your Fruit list and this time select only data cells without the column header. to avoid this happening by mistake, your fruits list should be formatted as a table and named tblFruits, in the name manager you can refer to the column name without select it by writing =tblFruits[Fruits] in the refer to box. hope this helps
Hi. I want to create 2 drop-down lists wherein the 2nd drop-down list is dependent from the first one. Now, the data for my 2nd drop-down list are results from a formula which include cells whose resulting values are "". The problem I'm facing is when I created my 2nd drop-down list. It include those cells with "" values which I don't want to happen. Any solution on this?
I am afraid I don't fully understand your question, if you send me your file I might be able to help, it seems to me that OFFSET function might help since you can control how long the list should be.
@@ELKAMELBI I already tried offset and it really does disregard those cells with "" formula result. But, say I have a validation list (Quarter) which allows me to select First, Second, Third, and Fourth from that list. Now, on another validation list, I want to be able to choose the list of learners who excel in each quarter. I'm thinking of using INDIRECT so that whatever I choose from the first validation list I would be able to only have the list of learners for that specific quarter on the 2nd validation list. But my data source (for the list of learners) is not a fix data which means it changes depending on my students performance. Hence, I'm having trouble creating the 2nd validation list. I hope I'm making sense..😅
Yes I think it is clear now what you’re after, can you send me your file with sample data in it, I think we can achieve this be Array formula, you can send the file to abdallah.elkamel(at)gmail(dot)com
Hi! Your video is really easy to work with as compared to others that I have viewed! However, its really weird that the (Indirect function) only applies to some name box! Appreciate if you could enlighten me on this! Been following suit but it just doesn't works! Some drop down doesn't works! :(
Thank you for the feedback, it helps to know I am being clear in my explanations. Regarding the issue you described, I suspect that you have spaces in your column headers name, is that right?
Ok, you need to use SUBSTITUTE function to to make this work, the function basically replaces all spaces with _ to match the name of the list, I will send you later the function and you just need to paste it and change only the cell name of the first list. I may need few hour before I can send it, thanks for your patience.
=INDIRECT(SUBSTITUTE($D2," ","_")) change the INDIRECT function you have with the one above where D2 is the cell of the first layer of the dependent list. for more explanation as to why: The reason for this because you are naming the lists by the header name and if the header name has space it will automatically be converted to underscore in the naming manager, so now when you call the list by referencing the first drop down menu value you need to replace spaces with underscore so it can be found in the naming manager. if you still need help, send me an email with your sample file.
You are great Data. I also have a question for you. Maybe it's the same problem as Nabaraj Pokhrel, maybe it's not. I have tried your formula with a list with more than 12000 rows. Everything went well, but suddenly, by making a new row, in the column 'Place' (in your example it's the column 'Food'), there is no dropdown list anymore. I have formatted it as a table, so why did it stop? Strange thing. All names under 'Place' ('Food') have spaces. Can this be the problem?
Thanks Nico, normally it doesn't matter how long the list is, and if 'Place' ('Food') has spaces we can add SUBSTITUE function to make it work. I suspect that you haven't used $ to lock the column but it is better if you can send me the file to my email: abdallah.elkamel(at)gmail(dot)com and i will certainly look at it and fix it for you.
This is the easiest tutorial i found on dependent drop down list without blanks! “Indirect” is way easier than “index”
I created a very complex spreadsheet that accomplished exactly what you explained in the video, but your version was much more effective, less time-consuming, reduces formulas and thus file size. When I get back to work, I will begin to incorporate what I have learned here. THANK YOU SO VERY MUCH.
Jeff, man! You’re welcome, thanks for the feedback, really appreciate it. you made my day, glad I could help. Let me know if you have any questions.
I looked at so many videos to learn this. This one explained it so easily and in the shortest amount of time! Great video
Glad to hear it! Thanks for the feedback 😊
This the easiest and fastest tutorial on dependent drop down without blanks. Thank you for this video! ☺️
Liked, and subscribed. Even added the alarm for new videos.
gald you liked it and thanks for the subs. hope I get time to publish new videos soon.
Dear sir, i watch many excel videos for years, today by chance i found your video which is unique navigation list, where i can look into menu of items to choose. it very simple but dynamic for my job. Keep up great work, i shall subscribe to learn more from you.Great Job!
You Sir have made my day 😊, thanks for the kind words and I am really glad that you found it useful.
Thanks a lot. i have tried with many other You tube videos about Dependent Drop down list but could not satisfied . Everything is in this video.
You're welcome, thanks for the great feedback
Still valid in 2019, thank you! If you want to use space or other letter in the main drop down list, you can replace them in the indirect selection:
=INDIREKT(BYT.UT(BYT.UT(B10;" ";"");"+";"")) (adapt to your language INDIRECT and SUBSTITUTE)
This removes any spaces and + in table header, but still visible in drop down list. Remove the same letters in the name definition.
That is correct Jens, SUBSTITUTE can fix the issue of not having spaces in column headers that is used as a list name in naming manager.
Thank you, this is superb. I was able to follow along even though I have a much older version of Excel on a Mac. This is the best explanation I have found.
This is a fantastic and simple to follow video!! Thank you very much for this guidance.
You're welcome, glad you liked it
This video is great, I am so thrilled to learn something I am struggling to address. Thank you.
I am so glad that you learned something here and hope to find more in the channel, best of luck and thank you for the comment.
I followed some other youtube videos on this subject but this one is the best. This is an excellent solution. I am impressed. liked and new subscriber to you channel.
wow thanks for the great feedback, it means a lot and give me a boost to do more, in fact I have improved this method a bit, keep an eye on the channel there will be a new video soon about this subject. thanks again :)
Best explanation and easy steps to follow - thank you so much!
Glad you like it Dawn, you're most welcome 🙂
easiest way to create a dependent dropdown list. thanks Man!!!
You're welcome and thanks for the feedback Diego. by the way, I have a new video soon that'll make it even better, keep an eye on the channel.
Very helpful, presented in a very simple and understandable manner!
Hey thanks :), glad you liked it Omprakash, I do try to simplify things because everything in fact is once you understand it.
Thank you so much! This is very helpful!
you're most welcome Nazri :)
Some pretty slick shortcuts. Explicit. Thanks
Thanks Type S, feedback like this keeps me going 🙏
Thank you Abdallah for showing how to do this practically. Great Job.! Helped me a lot.
You’re welcome Atul, glad you found it useful
Very helpful video. At 13:33, what we have to do so if food type is set to "Vegetables", "Broccoli" will not appear because it was selected earlier ? Thanks
Thank you Mohamed, yes that is a limitation i hope they fix obe day, you can fix it using vba.
If you have an issue with the indirect function, make sure the reference name (top row) doesn't have any spaces in between characters (ex. "Project Management" switch to >>> "Project_Management" or "PM"). Hope this helps.
Thanks for your feedback and you’re right, having no spaces is a best practice and makes things easier when creating lists, however, you can have spaces if you must but you need to also include SUBSTITUTE function to handle spaces.
@@ELKAMELBI And what to do when Name has: ( ) ? etc. Not to mention, when these are at the beginning of the Name or at the end?
SUBSTITUE Function should handle all those and you use it within data validation, let me know if you need help with that
@@ELKAMELBI I don't believe, Substitute can handle ALL of the invalid characters. E.g.:
: ; < = > ? @ ¢ £ ¥ { | } ~ « » !
How do you handle these all? Not to mention, that Excel does it differnetly in the name and at the end of the name...
I don’t see why not, however, I have used substitute with spaces so far, if you have an example maybe you can send it to me and I will have a look
THANK YOU!!!! Exactly what I needed and so useful. Excellent video with clear understanding.
Wow! You’re most welcome 🙏 really appreciate it, glad you got here 😊
Amazing and simple. Thanks a lot
Thank you 🙏
Great explanation and you made it so simple. Thanks!
You’re most welcome 🙏, glad you enjoyed it 😊.
Very neatly described. Thank you
Thank you Sumit, appreciate the feedback :)
been searching everywhere and i found the answer on your video thanks!!!
Glad you got here :)
Data Secrets Learning
I have one question if i would like to add a price to each element in the same table how could I define that ?
Thanks so much for an easy to understand presentation....from an absolute novice!
You're most welcome, and thanks for taking the time to put this comment, it really helps me to keep going, I am glad that the tutorial was easy to understand, I tried my best to make it clear for everyone, glad to see it worked :)
Very nice clear & creative lecture thanks
You're welcome Abdelhameed! glad you found it useful :)
Thank you and i just learn that we can use indirect function in setting the dropdownlist
Brilliant! You're welcome
I have no clue what I'm doing wrong, but I keep getting the error of "The Source currently evaluates to an error. Do you want to continue?" when I'm at the =Indirect() step of your video. Around 12:45.
Just a quick check, does your column headers have space in them? That might be the reason, if that’s the case, remove spaces from headers and try again. If you want to keep spaces in headers you need to use SUBSTITUTE function, I can help with if you send me the file by email.
@@ELKAMELBI Thank you for the quick reply! I removed all spaces from headers and table names and no luck. The project I'm working on will by quite tricky, but I was trying to do a test run with one section of my project. I'd be happy to send you an example, where might I find your email?
here it is: abdallah.elkamel(at)gmail(dot)com
Thank you so much. So much to learn from you brother.
Glad to hear that, thank you :)
Thank you very much for this explanation ...Helped me a lot.
Glad you liked it, thanks for the feedback 😊
Neat and Clean technique, awesome
Thank you :)
Great tutorial! I have tried using this with numbers but cant seem to get it to work. I keep getting an error (the source currently evaluates to an error). Is this because the name manager does not allow for #s to be used?
Thanks Dean, yes the name manager prevent you from naming any list by a number alone, the name in name manager has to start with either an underscore (_) or a letter, so to get it to work with numbers you need to add underscore to your names in name manager and when using to refer them prefix the cell that contain the number with "_"&. so if cell A1 contains the number that you've used as a name (prefixed by _), you can call the list by writing =SUM(indirect("_"&A1)). does that answer your question, if not send me a sample file at abdallah.elkamel@gmail(dot)com and I'll see what I can do. good luck...
@@ELKAMELBI worked like a charm! You sir....deserve a beer! thanks for the help.
Hey 👋🏻 glad it worked out for ya, Thanks for letting me know and the beer 🍺, sure i can use a cold one in this hot 🥵 summer.
i enjoyed your tutorial, *abdallah* … 'specially with regard to the _"indirect"_ function. you explained yourself almost exactly the way i explain myself … when instructing someone.
in the next few weeks … will try implementing _"dependent drop-down lists"_ within *libre-office* or *numbers* _(mac-os)_ … who knows, i might come away with a bit of luck.
Thanks so much Pnamajck for the kind words, I am glad you liked the video, Working on a video now to expand on this skill, the new way of dependent drop down list fixed the issue of having spaces in the column header and also a new list can be added automatically, keep an eye on the channel in the coming days, thanks again.
Explanation is good and it helps a lot! But i encountered problem on the list, the List it too Small, how can i change the Font Size? Thanks!
the only way is to zoom-in your sheet, the font size change size with zoom. maybe your sheet is already zoomed-out, 100% was ok for my eyes.
@@ELKAMELBI
thanks!!
Thank you so much for your great tutorial.
You're most welcome Sonjoy! glad you found it useful.
awesome topic
جزاك الله كل خير
مشكور وبارك الله فيك سيد أحمد
Hi, thanks for this tutorial, really useful... now, Is it possible to do the same drop-down list but instead of showing the value, show some text, for example, using a table of products and prices, and when choosing the product name, the value on the cell changes to the respective price, is it possible?
Yes, you use VLOOKUP function to get the price from the product/price table based on the product you select by a drop down list. so the first cell will be a drop down list that lists the products, once you select a product the cell next to it will take the product name you just selected and use VLOOKUP to get the price from the products/price table. I have a video on VLOOKUP on the channel you can check it out.
Excellent video- thank you!
You're welcome! glad it helped you.
Awesome and very helpful video! Thank you!
You’re most welcome 🙏, glad you found it useful.
@@ELKAMELBI Could you please help me further? --I entered "fruits" then selected "apples" first, then I changed the "Fruits" to "Vegetables", but "apples" still remains in the cell and i had to manually delete it. Is there a way to let it automatically go away since "Apples" doesn't belong to the new Food Type i selected? If i don't delete it, Data Validation didn't pop out any error message either. Thank you!
@@linm8649 for that to work you need to use VBA code to clear the cell after change happens in fruit/vegetable cell but data validation on its own cannot do that. managing lists in Excel is still weak to answer business questions specially when you start having dependent drop-down lists. if you look it up in google you will get a sample code that you can apply, I usually avoid using VBA as much as I can.
Such an important video to watch, it help me alot. Thanks you :)
You’re most welcome 🙏, make it Viral, Viral 😁.
Data Secrets Learning im just a new comer for ur channel, but guest what! I didn’t disappointed to sub on your channel 😊
@@chamnabkem5459 saw that, thanks for the subs :)
Many thanks Mr. Abdallah ! I have been looking for this one. How can I make prices automatically read from (tblPrices)? so we need only to enter the quantity manually.
Hi Mohammed, which video tutorial are you referring to? this video doesn't have tblPrices. are you referring to another video? thanks. to answer your question in general, VLOOKUP function is normally used to do this but give me more details, also you can contact me at abdallah.elkamel(at)gmail(dot)com
Is it possible to make the dynamic pulldown list into where they can select more than one like a checklist?
Not in Excel but you can in MS Access.
@@ELKAMELBI I have found some code in other videos, but I need to figure out how to exclude their code from applying to all of the pulldowns.
Was the vba code? Or do you mean formulas ? If vba code then yes because you can link it to a form. I don't vba code and I am not a fan of it. However, if it is a formulas that enables you to make multiple selections in a cell, I would like to see that, I am yet to see something like this without being overly complicated and involves redundant data.
Hi thanks for the awesome video. I wanted to know how I can add an item say maybe fruits or veggies later in the list. Thanks once again.
Indeed a very helpful quick video.
Glad it was helpful! and welcome to the channel Naveed!
Very useful. Thank you for sharing
Glad it was helpful!
Thanks for this i have a similar case but on gym thing not to make it complex i will set the question per your context
suppose for this case you need to quantity to populate automaticaly according to the chosen fruit type
how can i do it
i assume each fruit corresponds to a food type,
i mean each fruit type corresponds to a quantity
how can i have excel populate the quantity
i am using v look up but when i reference it to the second list i get an error
Thanks for your comment, the best way is to use VLOOKUP function to do that providing that fruit type names are unique, let me know if you need help setting that up.
+Data Secrets Learning the problem is that i tried the vlook up a million times but it keeps throwing an error #NA, when i reference it to The depedent list(Fruit list for this case)
Hmm, send me your file at abdallah.elkamel@gmail.com and I’ll see what I can do.
okay thanks
It is great, i have tried the food type list more than two (as you have presented in the video is only two) but could not able to make drop down list with the formula that you have used which is indirect........ so please help me ..
Does your column names have spaces? if yes, we need to use SUBSTITUE Function, let me know
@@ELKAMELBI Yes It Does Space
@@nabarajpokhrel2409 send me your file at abdallah.elkamel(at)gmail(dot)com and I will get it fixed for you. we need to use SUBSTITUTE function to replace every space with an _ underscore because if you automatically name a range that contain a space, excel will replace every space in the name with _ underscore
Hi. I am trying to create a drop-down menu which gives me an numerical value. For instance, I have 9 options I have for drop-down choice which have certain numerical value. How can I choose these as a question and when the answer is chosen, the number value populates in the cell next to it? Thanks
To achieve this you need a combination of a dropdown menu on one cell and VLOOKUP on the next cell where you the number to appear. the VLOOKUP function should navigate to a table that has 2 columns, the first one is the source to the dropdown menu and the second column contains the numbers that you want to show in the next cell after you make the selection from the dropdown menu, does that make sense? it is easier shown than explained in writing, so check this file I have just for your issue, a download button should be available at the top of the page, this is the link:
1drv.ms/x/s!AvVs0CO1qJZZqx2XZezbpAINxzsb
Hi. It isn't letting me upload it. Can I share a file with you to show you what I am after? It may help if you can visually see the document.
@@andywright7732 send me what you have at this email: abdallah.elkamel(@)gmail(dot)com
Hi Abdallah,Just sent it over.
Thank you so much for your video
Gald you liked it Kevin!
Do you have an updated version of this since the excel update?
Yes, I have a fully automated template that you can use over and over again. here a link to the video where you can find a link in the description to download the file ua-cam.com/video/k8p6sxRgfic/v-deo.html
This video was very helpful for me to learn the dependent drop down list, but one thing I want to learn is if i hve to bring automatically the price of the food in the next row then what formula i will have to use to get that. I will be glad if you answer to my question..waiting
That should be VLOOKUP, you need to have another table that has 2 columns, tbe first is tbe name of food and the second is the price.
Your video was really helpful but I have a problem cuz my first record of the first column of the table needs to be validation. How will the table expand automatically in this case?
Thanks, yes this is the one case where the table will not expand automatically. you either drag the bottom right corner of the table to add more rows, or use TAB. I prefer using TAB.
Data Secrets Learning Thank you for your answer! :) So if I fill the last cell of the table (for example the Qty entry in the video) and press TAB, it will automatically generate a new record below in the table?
Yes that is correct
Thank you very much! :)
such an amazing, easy and creative way
many thanks for this valuable content guys
You're more than welcome إبدأ, comments like yours encourages me to do more, it means a lot!
Great clip, very descriptive and a huge help but very blurry to watch.
Thanks, it is one of my old videos and i was not using the right software, i do agree it is blurry
Have a question, if my data has more cells populated how would this sample would be relevant.
Example: sheet 1 will be where the data will be presented, sheet 2 has aluminum whish has 4 cells populated as following cell one product code, cell two product name cell 3 description and cell 4 product weight (this cells will be multiplied by product cost (cell 5 on sheet1) then multiplied by length (cell 6 on sheet1) them multyplied by (cell 7 on sheet1) company profit.
thanks for the question Robert, you need to create a drop down list only for product code and it should be created in sheet 1 where you present your data, once a product code is selected in sheet 1 you use VLOOKUP to bring the relevant data for the product code selected like Product Name, Description, weight,...etc. once these are brought in sheet 1 you can then you can perform your calculations on it. it should be fairly easy to setup.
However, If you want your file to be setup professionally and maybe add more bells and whistles, I can do that for you for a small fee, let me know if that interest you, meanwhile feel free to ask me more questions if you have or clarifications on my answer above, thanks and good luck.
@@ELKAMELBI what the small fee would be?
Would you be kind and send me an email to abdallah.elkamel(@)gmail(dot)com , we can discuss this further and i can assure you will be happy.
Thank you Dependent drop down list video. Would it be possible if you could make video about how to create pivot table in excel in android tablets. And also another video about opening 2 excel files at the same time in android tablets. Thank you
I've completed all of the steps but get an error message when I get to the data validation that has =Indirect(C3). I shows a drop down but no content. Can anyone help me understand why that is? The error is not described at all, it just comes up when I try to close the data validation window. Thanks
Hi Diana, not sure why you’re getting this error, you can send me the file you worked on or I send you my file. Once I see your file I can figure out what’s wrong. My email can be found in the channel about section.
Very good and informative video
Thanks for your comment, glad you found it useful
Great tutorial, Abdallah....Thanks for taking the time to make this available
You're welcome Mike and thanks for your comment it means a lot and it keeps the fire lit, glad you got here and found the video useful :).
Hi, Trust are are doing well!!
What To do If Table name is not showing in the Excel sheet. I am trying to Make a sheet but unble to change the name of table because of its non availablity.
SOS !!!
I have sheet with thousands of data.
NAME as header in 1st Column , COMPANY as header in 2nd column and so on..
all the column have multiple repeated data.
So if I select any NAME it should only show the COMPANY where he has worked.
please help
please send your file to me at abdallah.elkamel(at)gmail(dot)com
@@ELKAMELBI please check
@@ELKAMELBI I appreciate your involvement in this project. Your support means a lot to me!
Thank you for helping me today.
This is what I was looking for a long.. Thanks a lot
Glad you got here 😊
Data Secrets Learning your the best
Art D, thanks so much! You made my day! 😊
hi.. what if i want to make a massive options on the first column? i still need to name the table one by one?? i have about 8.5k of data.
You’re in luck, a video will drop today hopefully with a ready template that has no limit :)
Great video. How would I change the color of the cell on the drop down menu depending on the answer? Such as red for no and green for yes...
Thanks, glad you found it useful. changing color of a cell depending on its value is done by utilizing what is known as "Conditional Formatting". if you haven't used that before, click first on the cell where you want to apply the conditional colors, click on the Home tab and then go to the right side of the screen and you should find the icon, clicking on it will give you options to choose from, select "Highlight Cells Rules"and from the submenu select "Text that contain...", enter the text (in your example, "Yes" or "No") and choose the color you'd like to use when that happens. remember, you need to do this process twice to define the two colors. let me know if you still have issues. there are abundance of conditional formatting videos on UA-cam that you can check include some in this channel. Thanks for your comment and glad you got here.
@@ELKAMELBI thank you!
Your video was extremely helpful, but somehow I'm stuck. When I try tp create a new row by either pulling down, doing "paste special" or by just typing in the empty row below and having, it still only uses the date from the first row with the main drop down list for example C3. The cells in column D will only show the dropdown for the first cell in column C. If anyone has a solution I would be grateful. Thank you!
Make sure the cell is not locked by dollar sign or at least the column should locked not tge row portion of the cell address
Hi,what if I wanted to add more items in the vegetables and fruits lists? How do I do that? Thanks.
You need to modify the list in the name manager, or I can send you a file that contain automated list additions, send me an email if you want the file
Thank you so much for the tutorial. It was a tremendous help. I encountered a problem where only some of my 'Foods' pulled through when selecting certain 'Food Types' and others worked perfectly. Where do you suppose the problem occured?
You're welcome Adri, glad you found it useful. now normally this happens if the named range is not covering all Foods, to fix that you need to extend the range to cover the missing items. now in the tutorial I reference column name since we are using formatted tables and that take care of it automatically. if you still facing issues you can send me your file at abdallah.elkamel(at)gmail(dot)com and I will do my best to help.
@@ELKAMELBI Hi Abdallah, after hours of trying I finally managed to get it to work, thank you so much.
I am glad you managed, thanks for letting me know, good luck 👍
Great tutorial!
Thanks, glad you liked it
There is a barely visible/very tiny text or number before you enter tblveges/ or tblfruit in the refers to box under new name. Please, Sir, what is that? Is it an asterisk, a tilde, an = , an arrow...... Located around 8:54, 8:53, 8:52.…. Sorry, disregard. It's an = sign
😊👌
Hi, I like your video and am trying to follow it but the name table function doesn’t come up like you demonstrated. And, I cant find name manager under Table Tools. Wondering what version you are using and what I am possibly doing wrong. I’m using office365 on onedrive. Thanks.
Thanks Dianne, I believe I was using 2013 version, but there should be no changes between 2013 and 365 which I am also using currently. Name manager should be under formula tab not table tools, and if your table header has spaces you need to throw SUBSTITUTE function in the mix. If you have a business need, I can setup your file for a small fee, I can be reached at abdallah.elkamel(at)gmail(dot)com, can also be reached on skype at abdallah.elkamel
Thanks ! I have made my day.
Glad you liked it 😊
Great video and explanation Sumit
Thanks 😊, glad you found it useful
Hi .Thanks for the video. It really helped me a lot. I have s small doubt. Will this not work if we use a space in between the table headers and drop downs?
You can but you will need to use substitute function to make it work, I think I helped someone with that in the comments of this video, if you still have issues you can either describe it here or you can send me an email at abdallah.elkamel(@)gmail(dot)com
Amazing stuff! thank you very much for the video. It was really helpful
You're welcome, glad you got here and found it useful, keep on learning :)
Thank you so much. You've got a new subcriber. :))
You're most welcome Anh :), glad you got here :)
@@ELKAMELBI I combine it with your guide of smart personal budget. It turns out really cool for me. Thanks again for both tutorials.
@@myanhdo1562 I am so glad you found it useful, good luck :)
Thank you for posting this video. Taking your example, after I select a particular fruit (Banana) value in column D (one of the fruits from dropdown list), I go back and change the value in column C from Fruits to Vegetables, my selection in column D is still showing Banana. But I would expect the cell value becomes null after I change in selection in column C. Can you please suggest someway how to handle this?
that is a common problem, it can be fixed by using vba code using AfterUpdate statement, i am not too fond of vba and i don't use it, if you want a fix for this look online for "clean a cell after update using vba" and most likely you will arrive at the right solution.
@@ELKAMELBI Thank you so much for a quick reply. I will continue to follow your channel.
You're so welcome!
مجهود جبار ما شاء الله. اخي،ارجو المساعدة، انشأت ملف كامل مشابه لهذه الفكرة. لبيانات خدمات عملاء وبالاخير الملف غير عملي بسبب كثرة الخدمات في القائمة الاساسية وصغر الخط وعدم وضوحها. هو جدول كبير وفيه قائمتين تتعلق ببعضهم البعض. ارجو الحل. كل الشكر لك يا اخي
في العادة، في هذه الحالات يتم استخدام اكسس بدل الاكسل، هل لديك فكرة على استعمالها؟
Thank you , I was wondering is it ok to creat this in a workbook that has 150 sheets and each one will have like 5 dependant dropndown list , couse I don't want to do all of it and ends up having a problem.
Another thing is that , is it ok to move or copy sheet that has defined name and still working the same way ? Couse I tried to do that but Excel said we cannot do this in a sheet that has tables. Thank you
You’re welcome Mohamed, I don’t know the maximum number of named ranges that Excel can handle, but I would question why you had to have 150 sheets in one workbook, there might be a better option like moving to an Access database or another platform. As for the other question, there shouldn’t be a problem copying or moving a sheet that contains tables or named ranges even if you copy to a new sheet, but you can’t have duplicate names, tables and named ranges need to have unique names. Hope that answers your question.
This is Recommendable, its Going to help me a lot thanks
You’re welcome Yodev, glad you found it useful
Thank you for the tutorial, it has help me understand Data Validations on a whole new level. With that being said, i'm having trouble with something.
So I highlight all my headers and give it a name(headers). I confirm that the headers come up in the Name Mgr (Department, Equip, Brand, Model). I have also created tables for each of these headers with information in their respective columns. The problem comes when on a separate sheet, I create the "form" and label it Department, Equip Type, Brand, Model Serial Number. I format all this as a table then I click on the cell under Department.
Following your instructions, I go to Data Validation, select List, the in the Source field i enter =headers. When I click on the drop-down box, it shows me the other column headers rather than the departments I had created tables for.
Any help is appreciated.
You're welcome, glad you've enjoyed it. regarding your question, it is hard for me to visualize what is happening with your file, if you send me your file at abdallah.elkamel@gmail.com I will be able to give you a comprehensive answer, thanks for the sub :)
Great! this will help me in my day to day works
Thanks Mary, glad you got here 🙏
good....i have one question for this. if i choose "Fruits" from fist drop down and in dependent menu i select "Apples". What if i goes to first drop down list and change to "Vegetables" it still stays "Apples" there. I want some error in the "Apples" drop down as i not changed to "Fruits to Vegetables" , how to achieve this error by color or popup?
Hey Rahul, it won’t give you an error by default, it may show green triangle at the edge of the cell to indicate ab error but that showed many times to be unreliable way to check errors. To achieve what you described, you use VBA, you should use AfterUpdate clause applied to Food Type, the code should clear the Food cell whenever Food Type cell is updated. Personally, I am not a fan of VBA, I prefer using Excel old school, but unfortunately there is no way that I can think of right now, maybe conditional formatting with a formula can achieve that! I will keep that in mind and if I run into a solution I will let you know.
@@ELKAMELBI Thanks for your quick response :)
It is apply only on same sheet .if we have list on other sheet it cannot apply.
In old Excel versions yes, but I believe from 2013 you can put the table in another sheet.
I am using Excel 2016 . But it cannot apply
@@gajanangalgale205 that is weird, it shouldn't be, 2016 should support having the list in another sheet. maybe the reason is related to something else.
Dear sir, i have one question? if i have third,fourth column etc. how to ref. "=indirect(c3) for rest of the column. pls.enlight.
Do you mean having three or four dependent lists? It can get complicated, because you need to, for example, expand all fruits to horizontal tables as you have done with the first list, you would have a table called apple that contains all types of apples and so on. I wouldn’t recommend more than two dependent lists unless it is absolutely necessary.
Also, if you have an example that you would like help with, send it over to my email: abdallah.elkamel(at)gmail(dot)com
This is perfect, but how would I do this with more than 1 set of subgroups?
For example I'm trying to have the first box give me the option to choose area, then the second box give me a list of equipment types in that area, then the third box give me a list of all the equipment that is listed as that equipment type.
Further Example:
Box A(area) - Cold End
Box B(equipment type) - Robots
Box C(specific machine) Robot #3
OR
Box A(area) - Ware House
Box B(Equipment Type) - Conv.
Box C(Specific Machine) - Conv. #3
So when I select a certain option in Box A it gives me a certain range of options in Box B that wouldn't be there if I chose a different option in Box A. And when I choose a certain option in Box B(that correlates with Box A) it gives me a selection of options in Box C that would be different if I chose a different option in Box B.
If I chose:
Box A(area) - Cold End
Box B(equipment type) - the options for warehouse aren't there. Only the ones for Cold End are.
(Cold End, Warehouse, Robot, Conveyor are just examples.)
How would I make this happen? Thank you.
Thanks for your question, the way to do it is to create a new set of lists with headers named exactly as those in Box B, under each list header put all those options from Box C that correlates with the header of that list, then follow the same data validation procedure described in the video. If these instructions are not clear, I can create a file for you that you can populate with your data later on, you can even send me a sample of data that I can work on :).
I want it to just ADD the digits to the right of the decimal ONLY, if the integer begins with a negative sign. Is this possible?
sorry for the late reply, your comment for some reason went to Spam, just found it about it. is your question related to the video or it is a general question? i am afraid I don't understand what you're after.
@@ELKAMELBI It's just related to Excel.
@@Berghiker can you explain what you mean by "if the integer begins with a negative sign", does this mean your negative sign is on the right side? it maybe better if you send me an email at abdallah.elkamel(at)gmail(dot)com, maybe include an example. thanks
@@ELKAMELBI -1.2 -1.5 -1.08 are all in a column.
I want it to add up the .2+.5+.08
@@Berghiker this formula should do it =SUM(MOD(ABS(A1:A3),1)) where A1:A3 is where the negative numbers are. hope this helps
Simple and well explained
Thank you sir, I appreciate it 🙏
hi. thanks for the video. i have question. how to add price automatically when are selecting the drop down item? i mean when you were selecting food. automatically adding price
You're welcome Aldi, glad you liked the video. to make price appear automatically you need to use VLOOKUP function but before that you need to create a list of all your products and their prices, what VLOOKUP does is take the selected item from your drop down list and then go to the price list search for the item in the leftmost column and then pick the corresponding price. if you'd like me to help you send your file to abdallah.elkamel(at)gmail(dot)com.
Great video . Thanks
You’re most welcome Martin, glad you found it useful and thanks for the subs
If I want to create one more drop-down list under the Vegetables>Broccoli>"Types of Broccoli" how could I do it
Doable, but a bit harder to explain and requires a bit of management as you add more categories, basic idea is that the items in the second list will become a header in tree of third list. If this is something you need, send me your file and I will see what I can do.
@@ELKAMELBI Thanks, I sent Excel file, you can examine my problem.😃
you can call me from as I sent my mail from my mail address
Ok will check once I get some free time 🙂
Hi, great video, thank you for making this. I have it working almost perfectly, except when i pull down the second menu, the name of the first menu is still on top of the dropdown, so like if i chose Fruits, then the second dropdown first option is Fruits, then all of the fruits. any idea what i am doing wrong? I don't want "Fruits" to display on the second dropdown.
thanks!
apparently you have included the header cell when you selected the cells for the second list. to fix this go to Formulas tool bar > Name Manager > find your Fruit list and this time select only data cells without the column header. to avoid this happening by mistake, your fruits list should be formatted as a table and named tblFruits, in the name manager you can refer to the column name without select it by writing =tblFruits[Fruits] in the refer to box. hope this helps
@@ELKAMELBI yes, I selected the table when i names it the second time. when i just created it with the tblfruits it worked correctly. thank you-
very good and advanced lessen
Thanks 😊, glad you liked it
Very helpful thanks so much.
You're welcome, glad you found the video useful :)
thanks you very much sir, it has been helpful
You're most welcome :), glad you found it useful
Can we add another drop down list dependent on the food type ?
You can if your data is not that big but I wouldn’t recommend it unless it is really needed.
Hi. I want to create 2 drop-down lists wherein the 2nd drop-down list is dependent from the first one.
Now, the data for my 2nd drop-down list are results from a formula which include cells whose resulting values are "".
The problem I'm facing is when I created my 2nd drop-down list. It include those cells with "" values which I don't want to happen.
Any solution on this?
I am afraid I don't fully understand your question, if you send me your file I might be able to help, it seems to me that OFFSET function might help since you can control how long the list should be.
@@ELKAMELBI I already tried offset and it really does disregard those cells with "" formula result.
But, say I have a validation list (Quarter) which allows me to select First, Second, Third, and Fourth from that list.
Now, on another validation list, I want to be able to choose the list of learners who excel in each quarter. I'm thinking of using INDIRECT so that whatever I choose from the first validation list I would be able to only have the list of learners for that specific quarter on the 2nd validation list.
But my data source (for the list of learners) is not a fix data which means it changes depending on my students performance. Hence, I'm having trouble creating the 2nd validation list.
I hope I'm making sense..😅
Yes I think it is clear now what you’re after, can you send me your file with sample data in it, I think we can achieve this be Array formula, you can send the file to abdallah.elkamel(at)gmail(dot)com
@@ELKAMELBI Thanks but I already found a solution and had been able to setup the validation list.
@@CloudStudio1 Glad you managed :)
Really did not know this
Gr8
Thanks for the feedback, glad you found this useful 😊
Hi! Your video is really easy to work with as compared to others that I have viewed! However, its really weird that the (Indirect function) only applies to some name box! Appreciate if you could enlighten me on this! Been following suit but it just doesn't works! Some drop down doesn't works! :(
Thank you for the feedback, it helps to know I am being clear in my explanations. Regarding the issue you described, I suspect that you have spaces in your column headers name, is that right?
@@ELKAMELBI yes! My 1st layer is Website (Book A Vist) and it won't works for the 2nd layer! 🤣
Ok, you need to use SUBSTITUTE function to to make this work, the function basically replaces all spaces with _ to match the name of the list, I will send you later the function and you just need to paste it and change only the cell name of the first list. I may need few hour before I can send it, thanks for your patience.
=INDIRECT(SUBSTITUTE($D2," ","_"))
change the INDIRECT function you have with the one above where D2 is the cell of the first layer of the dependent list.
for more explanation as to why:
The reason for this because you are naming the lists by the header name and if the header name has space it will automatically be converted to underscore in the naming manager, so now when you call the list by referencing the first drop down menu value you need to replace spaces with underscore so it can be found in the naming manager.
if you still need help, send me an email with your sample file.
@@ELKAMELBI hi and let me give it a try!! 🤣
You are great Data. I also have a question for you. Maybe it's the same problem as Nabaraj Pokhrel, maybe it's not. I have tried your formula with a list with more than 12000 rows. Everything went well, but suddenly, by making a new row, in the column 'Place' (in your example it's the column 'Food'), there is no dropdown list anymore. I have formatted it as a table, so why did it stop? Strange thing. All names under 'Place' ('Food') have spaces. Can this be the problem?
Thanks Nico, normally it doesn't matter how long the list is, and if 'Place' ('Food') has spaces we can add SUBSTITUE function to make it work. I suspect that you haven't used $ to lock the column but it is better if you can send me the file to my email: abdallah.elkamel(at)gmail(dot)com and i will certainly look at it and fix it for you.