I've read so many guides and watched so many videos about how to do this, and never could figure it out despite that. And after all that, I can't even believe how easy you just made it. I really thought it would take an act of god, and all this time it's been just that simple. Absolutely ridiculous. Thank you so much!!!!
This was brilliant! I was trying to figure this out literally all day. (I’m stubborn) I searched all over and you were the first presenter to identify this specific issue and how to fix it. THANK YOU!
I have been trying to do this for 3 hours now and your video somehow was the one (of 7 videos and many websites) that actually helped. Thank you very much!
Thanks Sir, your video was really helpful!!! It resolved the issue that repeatedly checked/trying to resolve by different data analysts. I was trying to check different videos but your beat remaining. Thanks again
@computergaga, thank you for providing the most needed solution to the problem at hand. However, I have tried it with items with very long sentences but still giving me errors. Any additional guidance and solution to this? Looking forward to hearing from you and thank you once more.
Excellent how to that solved an issue I was having so thanks for that. Question if I wanted to have 5 or 6 drop down boxes that look to the last for where to get its next list is that possible. Say country then city then region then street then door number.
Dear Alan, thank you very much for sharing your knowledge! I've been trying to find a solution by myself, but finally I gave it up and started searching on the internet. Your explanation is the best ;)
Thank you so much! I am trying to use drop down option names that include a dash (Ex. No - South Africa, No - Germany). What is the best way to nest the Substitute formula to include the dash as well?
Can you help me with another query? When I convert .xls to .csv, a space is introduced before _. How do I make sure that the data remains intact while converting to csv?
You're welcome, Sruthi. You would handle them in the same way as the video, but you may need the CHAR function to help work with the special character - depends on the character and your region etc.
Hello, Great guide overall except that I am seeing an error: " The Source currently evaluates to an error. Do you want to continue? If I press "Yes", the list alternatives in column B(City) is empty. Please help!
@@Computergaga Correct, and it solved my "Germany" values. However, when I switch "Country" to "SouthAfrica" (Yes, I am trying without spaces) - the "City" column is unclickable and I cant pick any values. Any thoughts?
Excellent, thanks for ur clarification. if we have special character in the list what we have to do ? (Ex:- "south africa" instead of " South africa_New "
Thank you sir, it works now. But i have another question that the list item i have PY234, and it showed in name range is PY234_, how can i remove the underscore? Thank you so much
Another way to do this is use the create named range from selection tool. It replaces spaces and special characters that named ranges can't handle with an underscore.Then similarly to substituting the space with an empty string, substitute the space for the underscore.
Hi, Could you help me on this question? for example imagine that you already set the name of city as a value choosed from de dropdown E.G. You choosed United Kingdom but i'd idlike to replace united kingdon to name United States and update all values already set in the cell. In the drop down it has been updated but the cell remains Thee old value. how to do that? Thank you
Hi, I have a question. I followed this but it doesn’t give me the exact list that I have. Some were correct and some were not. Example: South Africa, type as southafrica. How do I type the name for Available Promotions/Offers? Also for Change/Upgrade Order? Thanks for your help!
Thank you very much. i gained sufficient knowledge about this topic. Explanation of this video is very clear and easily understandable. Thanks for sharing knowledge
The substitute space rule is great but how can I add a 2nd rule within the same data source line. I want to substitute spaces for no space and “&” symbols for no “&” symbol. I thought this would work but I got an error. =INDIRECT(SUBSTITUTE(A2,” “,””)or(A2,”&”,””)) Thank you for your help with this!
Hello, this is a wonderful tutorial, especially in combination with the one about creating multiple dependent drop down lists. My knowledge of excel is basic and these vids helped me greatly. However, I'm having the same problem as mentioned below: I have a column with years. Now, I've managed to get it to work with this workaround for the first entry. I simply named the 2014 selection Fourteen and used the described substitute method. But now it only works for the year 2014, not 2015, 2016, 2017 & 2018. Is there a way I can include those years within the same indirect/substitute formula, so the whole column will function the way just the first cell/2014 does now?
Never mind, I found a way! I used an online tutorial from elsewhere which used it for illegal characters. For those experiencing the same issue; it's rather easy and involves Lookup Tables and does require a different workaround than this vid. The below solution works for all "illegal" characters, like ampersands (&), asterisks (*) and numbers. The issue I was having was with my Year column, as Excel couldn't recognize the years when using it to create a dependable drop down. First, set up a lookout table by writing the desired characters in one column and a short code in the second column. In my example, this became my Year column (2014, 2015, 2016, 2017 and 2018) as the left column from B2:B6, and in the right column I created the "code": Fourteen, Fifteen, Sixteen, Seventeen and Eighteen from C2:C6. Then you select B2:C6 and create one range, in my case called YearLookup. Now, go back to the Drop Down tab or whatever you've called it (the above is all done in the list tab). You should already have the regular drop down for Year here (data > validation > list > =Year) or whichever illegal characters you've been using in the drop down menu from which you want to create a dependable drop down menu. For me that's in A2:A200. The formula you'll be using is as follows: =INDIRECT(VLOOKUP(A2;YearLookup;2;0)) Click on the cell(s) you want to use for the dependable drop down menu. Go to data > validation and select lists and type in that formula. If your Year or similar column is elsewhere on the sheet, then replace the A2 and replace YearLookup with what you've called your Lookup range. The 2 is used for the lookup formula and basically means the coded message is in the second column. The zero should stay a zero. It should work now by simply allowing you to select the year in the first drop down menu and then getting to choose a value from the dependable drop down menu! I've even used the B2 (in which the formula is used in my case) to create another dependable drop down menu the old fashioned way (because no spaces or illegal characters) farther in the sheet. If you need more illustrations, then look at the tutorial I've based this on: blog.contextures.com/archives/2013/09/26/conditional-drop-down-lists-in-excel/
I would suggest to use an underscore in the named range because you cannot start with numbers, but can use them e.g. _2014, _2015, _2016 for the named ranges. In the cells would just be the normal 2014, 2015, 2016 etc. Then for the formula in Data Validation =INDIRECT("_"&A2)
Can someone help me with the error "The Source currently evaluates to an error. Do you want to continue?. I made two dependent drop down list successfully but getting this error on the third drop down list. I can share the file as well.
Just a question: What if I change the Country, say Germany to South Africa, is there a way that can warn me that I need to change as well the City like a error message on the next coloumn? Please help me. Thanks. 😊
Sure. In the next column, you could use a formula such as this =IFERROR(MATCH(B2,INDIRECT(SUBSTITUTE(A2," ","")),0),"Please change the city") to look for the city down the chosen countries column. If not found then display some text.
I would like to know how to put data validation more that 2 words and including symbols When I tried with your formula its not working Ex:- Palm five international (Pvt) Ltd
Sure. spaces and symbols are not allowed in the Name Manager. So this video showed how you could have spaces in a cell, but obviously not in the name. So SUBSTITUTE was used to replace them. You can do this for anything. So you could have () in a cell, but not in the name. Multiple SUBSTITUTES would need to be used. Since doing this video, in Office 365 the FILTER or the XLOOKUP function - ua-cam.com/video/sTj3KnbGFhM/v-deo.html can also be used to create dependent drop down lists.
Hiya, Thanks a lot for the simplicity of this video, really helpful so far. Just a couple of questions- Would it then be possible to create a dependent list for when the category has more than one space e.g instead of 'United Kingdom', we have 'The United kingdom' -- if that's the case, would this resolution apply? I have tried but doesn't work. I also tried with the instance of something like -- 'America/Europe'. Is it possible to have this as well with the /....... Thanks in advance.
Good evening. Is it possible to make a dropdown list in C2 dependent on the answer in A2 as well? I followed your steps and it worked for me for the dropdown list in B2. But when I wanted to apply the same rule in C2, I got an error? Could you please explain to me why and how I can solve this problem? Thank you for your time and consideration.
I'm not sure what the problem would be without seeing it Alejandro. You should be able to perform the same steps that you did in B2. I would try again, I'm sure it will be fine. Check your typing and also be wary of fixing references if you are copying and pasting the cell.
Hello, thank you for the video. What can I do if when I enter the function it results in telling me theres an error? This keeps happening when I try to use the indirect function
I can't say without knowing the error or seeing it. If the cell it is dependent upon is blank, it will warn you of errors. Just carry on and try it. Ensure something is chosen from the first list.
Thank you for the explanantion but i followed exactly and it pop out ...the source currently evaluate to an error, i've tries using simple alphabets like a,b,c but it still pop out the same message, appreciate your help.
what if for words like "Slickline and Coil Tubing" ? can i use this substitue term or need another term? for this only two words "South Africa" "United Kingdom"
Sure, you can use SUBSTITUTE still. It will replace/remove all spaces. Just be sure the name and the listitem match. So the name would need to be SlicklineandCoilTubing to match the list item minus spaces.
I was wondering, the place I work uses a lot of technical manuals, with lots of sub-paragraphs etc. I have been working on a workbook with drop downs to make searching references easier. Fortunately, the parent drop down begins with the word “section” so there is no problem defining the name. However, there after the paragraphs are numbered 1.4, 2.6.8 etc. rather than typing _1.4 (Don’t think I can even use the.) is there a way I can use a number at the beginning and still use the subtract formula for spaces? Thank you,
@@Computergaga I need to include in my dependent drop down list name ranges starting with number, also including charterers " / ", " &", "-" and " ' " . What should I write in Substitute formula? Thanks in advance
I just made a video of creating dependent lists with combo boxes on a userform - ua-cam.com/video/3VTq_kyCJec/v-deo.html I do not have one for combo boxes on a worksheet though.
I have a bit different question… i read many comments but nobody has my issue, when i close my file and reopen it the list disappears, so i have to recreate it again, any solution please And thank you so much
Hi!! I'm looking for a solution to my problem:- I want to have a drop down list with country names appearing in it, however when any of the country name is selected in drop down, it should automatically show the value of country code, which is on the adjacent column of country name.
Nice. Very helpful when list items contains spaces. I wish if something like this could work for strings starting with special characters and numbers also. I've entries like "1abc", "@Comm" in my first drop down list on the basis of which I need to filter elements in second drop down. I've heard there is some concept of aliases while naming cell ranges but I'm still exploring if that is really a possibility which can be helpful in this case.
You can hopefully use a similar technique to the video Rasik. The cell would contain the special characters, but the name would use an alternative word maybe. You can then substitute the name for the symbol just like we did for the spaces.
So I tried this and found that the second dropdown list does not show up. I tried recreating the second list but still running into the same issue. Any suggestions? I also can't seem to re-name lists in that first box at the top, it stays as the Cell(B2) no matter how I enter the list name.
@@Computergaga Tried that and it still doesn't stay. Is there a way for me to do "indirect" to a cell on my list page? while still like list names with spaces
@@Computergaga I guess the issue now is that I can't seem to change the named range at all, even when I have no space and press enter. It's really strange cause all other guides do what you do but for some reason, my excel doesn't seem to do that. I will try starting from scratch and see what I have missed.
If you choose something from the 1st dropdown list you get the correct list in the 2nd dropdown list . But if you choose some thing from 2nd dropdwon list and you changed your mind in the 1st dropdown list ... the 2nd dropdown list will not clear out or reset ad keep the old chosen value ... how to get around this
Excel me data validation problem The list source must be a delimited list; or a reference to single row or Column Mere me te likh raha hai aur kam nhi kar raha hai please help
Hi, I'm probably doing something wrong. I tried to do the exact same thing in excel but when I enter the SUBSTITUE formula it says the formula isn't right. =indirect(substitute(A2," ",""))
That shouldn't be a problem. Select the entire column and then in the formula reference row 1. For example B1. Even those this is a header and not a list in that cell, it needs to start here if you select the whole column.
Bar none one of the most frustrating efforts ever. Excel is sometimes a colossal waste of time. The infamous "There is a problem with this formula. Not trying to type a formula?" error message will have you screaming. Even the MS Help doesn't know how to make this work once that message appears. MS VBA = lame. Sorry this simply doesn't work.
I've read so many guides and watched so many videos about how to do this, and never could figure it out despite that. And after all that, I can't even believe how easy you just made it. I really thought it would take an act of god, and all this time it's been just that simple. Absolutely ridiculous. Thank you so much!!!!
You're welcome. Glad to be of help.
EXCELLENT, ITS WORKED. I WASTED 2 DAYS OF MY TIME FOR SEARCHING SOLUTION. FINALLY I GOT HERE. THANKS A LOT TO YOU SIR
Happy to help Krishna.
YOU are so clear and helpful... tried so many videos.. none as clear as you thank you so much!
Glad it helped! Thank you for the comments Sandra.
I have been struggling with this for 3 hours and finally found your explanation! Thank you for saving the remnants of my sanity!
Glad it helped. Stay sane 👍😊
This was brilliant! I was trying to figure this out literally all day. (I’m stubborn) I searched all over and you were the first presenter to identify this specific issue and how to fix it. THANK YOU!
Glad I could help!
I have been trying to do this for 3 hours now and your video somehow was the one (of 7 videos and many websites) that actually helped. Thank you very much!
You're very welcome. Glad I could help.
Hi, Do you have a video that allows you to do this "indirect" function on more than 1 column or data validation, please?
Thanks Sir, your video was really helpful!!! It resolved the issue that repeatedly checked/trying to resolve by different data analysts. I was trying to check different videos but your beat remaining. Thanks again
@computergaga, thank you for providing the most needed solution to the problem at hand. However, I have tried it with items with very long sentences but still giving me errors. Any additional guidance and solution to this? Looking forward to hearing from you and thank you once more.
Excellent how to that solved an issue I was having so thanks for that.
Question if I wanted to have 5 or 6 drop down boxes that look to the last for where to get its next list is that possible.
Say country then city then region then street then door number.
Sure. You can continue in the same vain. I have a video on this here - ua-cam.com/video/5nb84p2wX-c/v-deo.html
Dear Alan, thank you very much for sharing your knowledge! I've been trying to find a solution by myself, but finally I gave it up and started searching on the internet. Your explanation is the best ;)
Thanks Jurij. Much appreciated.
Thank you so much! I am trying to use drop down option names that include a dash (Ex. No - South Africa, No - Germany). What is the best way to nest the Substitute formula to include the dash as well?
Your tutorials are absolutely perfect. So easy to follow. Thanks for posting mate.
Thank you very much Glenn.
Thank you for the video. It was indeed helpful. I have a query. What if the named ranges are seasonal_fruits and seasonal_vegetables?
Thanks Deepa. And the cell text was seasonal fruits and seasonal vegetables? Following the tutorial, replace the "" with "_".
@@Computergaga yeah..I tried and it worked..thank you so much!
Excellent!
Can you help me with another query? When I convert .xls to .csv, a space is introduced before _. How do I make sure that the data remains intact while converting to csv?
I don't know the answer to this Deepa.
Thank you so much. Can you please tell how to do validation where special characters are present.
You're welcome, Sruthi. You would handle them in the same way as the video, but you may need the CHAR function to help work with the special character - depends on the character and your region etc.
@@Computergaga Thank you, but how do I put it in the formula if the actual column has both spaces and special characters ?
Thanks
Thanx mate, was lookin to solve this problem for days. Thanx alot
No problem Putu.
Hello,
Great guide overall except that I am seeing an error:
" The Source currently evaluates to an error. Do you want to continue?
If I press "Yes", the list alternatives in column B(City) is empty.
Please help!
Thank you. It sounds like there is nothing in the first drop down cell.
@@Computergaga Correct, and it solved my "Germany" values. However, when I switch "Country" to "SouthAfrica" (Yes, I am trying without spaces) - the "City" column is unclickable and I cant pick any values. Any thoughts?
@@Computergaga THIS DOESNT HELP THE ISSUE.
Excellent, thanks for ur clarification. if we have special character in the list what we have to do ? (Ex:- "south africa" instead of " South africa_New "
Thank you so much , it is fabulous! I have the problem for the list items contain brackets, how can i remove it, thank you!
Thank you, Polly. They can be removed with the same technique. You would need a SUBSTITUTE function for each bracket.
Thank you sir, it works now. But i have another question that the list item i have PY234, and it showed in name range is PY234_, how can i remove the underscore? Thank you so much
thanks a lot, but how can we create rows dependent on a drop down column list? is there a way to do it?
It sounds like you may need some kind of lookup. So when an item is chosen from a list, another cell returns a value relating to that item?
WOW!!! Saved my life! Thank you very much!!!
Glad it helped 👍
Another way to do this is use the create named range from selection tool. It replaces spaces and special characters that named ranges can't handle with an underscore.Then similarly to substituting the space with an empty string, substitute the space for the underscore.
Hi, Could you help me on this question?
for example imagine that you already set the name of city as a value choosed from de dropdown E.G. You choosed United Kingdom but i'd idlike to replace united kingdon to name United States and update all values already set in the cell. In the drop down it has been updated but the cell remains Thee old value. how to do that?
Thank you
Fantastic functions and great tutorial! Thanks!!!
You're very welcome, Helena. Thank you.
Hi, I have a question. I followed this but it doesn’t give me the exact list that I have. Some were correct and some were not. Example: South Africa, type as southafrica. How do I type the name for Available Promotions/Offers? Also for Change/Upgrade Order? Thanks for your help!
Thank you very much. i gained sufficient knowledge about this topic. Explanation of this video is very clear and easily understandable. Thanks for sharing knowledge
My pleasure Chathura. Glad to be able to help.
This is what I was seeking… thanks
You're welcome, Kim 👍
This is super helpful! Thanks 😊
You're welcome.
simple but very effective, awesome work
Thanks Goldy.
The substitute space rule is great but how can I add a 2nd rule within the same data source line. I want to substitute spaces for no space and “&” symbols for no “&” symbol.
I thought this would work but I got an error.
=INDIRECT(SUBSTITUTE(A2,” “,””)or(A2,”&”,””))
Thank you for your help with this!
Would this work for hypens or special characters?
Sure! 👍 The same approach with SUBSTITUTE could replace any character that exists in a cell but not allowed in a defined name.
Hi could you help me with a problem on this
Hello, this is a wonderful tutorial, especially in combination with the one about creating multiple dependent drop down lists. My knowledge of excel is basic and these vids helped me greatly. However, I'm having the same problem as mentioned below: I have a column with years. Now, I've managed to get it to work with this workaround for the first entry. I simply named the 2014 selection Fourteen and used the described substitute method. But now it only works for the year 2014, not 2015, 2016, 2017 & 2018. Is there a way I can include those years within the same indirect/substitute formula, so the whole column will function the way just the first cell/2014 does now?
Never mind, I found a way! I used an online tutorial from elsewhere which used it for illegal characters.
For those experiencing the same issue; it's rather easy and involves Lookup Tables and does require a different workaround than this vid. The below solution works for all "illegal" characters, like ampersands (&), asterisks (*) and numbers.
The issue I was having was with my Year column, as Excel couldn't recognize the years when using it to create a dependable drop down. First, set up a lookout table by writing the desired characters in one column and a short code in the second column. In my example, this became my Year column (2014, 2015, 2016, 2017 and 2018) as the left column from B2:B6, and in the right column I created the "code": Fourteen, Fifteen, Sixteen, Seventeen and Eighteen from C2:C6. Then you select B2:C6 and create one range, in my case called YearLookup.
Now, go back to the Drop Down tab or whatever you've called it (the above is all done in the list tab). You should already have the regular drop down for Year here (data > validation > list > =Year) or whichever illegal characters you've been using in the drop down menu from which you want to create a dependable drop down menu. For me that's in A2:A200.
The formula you'll be using is as follows: =INDIRECT(VLOOKUP(A2;YearLookup;2;0))
Click on the cell(s) you want to use for the dependable drop down menu. Go to data > validation and select lists and type in that formula. If your Year or similar column is elsewhere on the sheet, then replace the A2 and replace YearLookup with what you've called your Lookup range. The 2 is used for the lookup formula and basically means the coded message is in the second column. The zero should stay a zero.
It should work now by simply allowing you to select the year in the first drop down menu and then getting to choose a value from the dependable drop down menu! I've even used the B2 (in which the formula is used in my case) to create another dependable drop down menu the old fashioned way (because no spaces or illegal characters) farther in the sheet.
If you need more illustrations, then look at the tutorial I've based this on: blog.contextures.com/archives/2013/09/26/conditional-drop-down-lists-in-excel/
I would suggest to use an underscore in the named range because you cannot start with numbers, but can use them e.g. _2014, _2015, _2016 for the named ranges. In the cells would just be the normal 2014, 2015, 2016 etc.
Then for the formula in Data Validation =INDIRECT("_"&A2)
Thank you for teaching best trick
Most welcome
MVP of the century
😊 Thank you.
How can u apply it to multiple row??
Can someone help me with the error "The Source currently evaluates to an error. Do you want to continue?. I made two dependent drop down list successfully but getting this error on the third drop down list. I can share the file as well.
Try selecting something in the first list and check if the dependent one works.
It may work despite this warning.
Sama problem it does not work @@Computergaga
Just a question: What if I change the Country, say Germany to South Africa, is there a way that can warn me that I need to change as well the City like a error message on the next coloumn? Please help me. Thanks. 😊
Sure. In the next column, you could use a formula such as this =IFERROR(MATCH(B2,INDIRECT(SUBSTITUTE(A2," ","")),0),"Please change the city") to look for the city down the chosen countries column. If not found then display some text.
I would like to know how to put data validation more that 2 words and including symbols
When I tried with your formula its not working
Ex:- Palm five international (Pvt) Ltd
Sure. spaces and symbols are not allowed in the Name Manager. So this video showed how you could have spaces in a cell, but obviously not in the name. So SUBSTITUTE was used to replace them. You can do this for anything. So you could have () in a cell, but not in the name. Multiple SUBSTITUTES would need to be used.
Since doing this video, in Office 365 the FILTER or the XLOOKUP function - ua-cam.com/video/sTj3KnbGFhM/v-deo.html
can also be used to create dependent drop down lists.
What do I do if it keeps giving me the error of "The list source must be a demimited list, or a reference to single row or column"
Hiya, Thanks a lot for the simplicity of this video, really helpful so far.
Just a couple of questions- Would it then be possible to create a dependent list for when the category has more than one space e.g instead of 'United Kingdom', we have 'The United kingdom' -- if that's the case, would this resolution apply? I have tried but doesn't work. I also tried with the instance of something like -- 'America/Europe'. Is it possible to have this as well with the /....... Thanks in advance.
I figured this out in the end. Thanks
@@lilyigwe1633 I am facing this issue too, Can you tell me how to fix it ?
Thanx, but same condition work without VBA and not required userform. Only two combo box required for dyanmic range automatically select.
Thank you!
New subscriber here!
Excellent! You're welcome.
Good evening. Is it possible to make a dropdown list in C2 dependent on the answer in A2 as well? I followed your steps and it worked for me for the dropdown list in B2. But when I wanted to apply the same rule in C2, I got an error? Could you please explain to me why and how I can solve this problem? Thank you for your time and consideration.
I'm not sure what the problem would be without seeing it Alejandro. You should be able to perform the same steps that you did in B2.
I would try again, I'm sure it will be fine. Check your typing and also be wary of fixing references if you are copying and pasting the cell.
Hello, thank you for the video. What can I do if when I enter the function it results in telling me theres an error? This keeps happening when I try to use the indirect function
I can't say without knowing the error or seeing it. If the cell it is dependent upon is blank, it will warn you of errors. Just carry on and try it. Ensure something is chosen from the first list.
Thank you for the explanantion but i followed exactly and it pop out ...the source currently evaluate to an error, i've tries using simple alphabets like a,b,c but it still pop out the same message, appreciate your help.
Nice video 👍👍👍
Thank you 👍
what if for words like "Slickline and Coil Tubing" ? can i use this substitue term or need another term? for this only two words "South Africa" "United Kingdom"
Sure, you can use SUBSTITUTE still. It will replace/remove all spaces. Just be sure the name and the listitem match. So the name would need to be SlicklineandCoilTubing to match the list item minus spaces.
@@Computergaga so i can just use the same code like urs for two words? or need a new one for the 4 words?
i mean for the dependant drop down list
Same code as mine works for any number of words Syukur 👍
Nice trick! Thank you Bud
You're very welcome.
I was wondering, the place I work uses a lot of technical manuals, with lots of sub-paragraphs etc. I have been working on a workbook with drop downs to make searching references easier. Fortunately, the parent drop down begins with the word “section” so there is no problem defining the name. However, there after the paragraphs are numbered 1.4, 2.6.8 etc. rather than typing _1.4 (Don’t think I can even use the.) is there a way I can use a number at the beginning and still use the subtract formula for spaces?
Thank you,
Hi Eugene, you cannot begin a named range with a number, but you can use them. Defining a name of Section1.4 should be ok.
Awesome!!!! Thank you very much!!!!!!
You're welcome Noe.
Very helpful video!!!!
Great to hear. Thank you Tamara.
@@Computergaga I need to include in my dependent drop down list name ranges starting with number, also including charterers " / ", " &", "-" and " ' " . What should I write in Substitute formula? Thanks in advance
You would need a SUBSTITUTE function for each character to include.
Awesome Due to Space issue in the top row list was reflected the error
What if i have numbers, multiple words (more than 2) or special signs like &?
The same solution can be applied. You can handle the characters or the numbers the same way that we handle the spaces.
Thank you very much. I was facing this issue and unable to find solution.
You are welcome Shirish. Thank you.
BRAVO!!!! Thanks!
Thanks Sandy.
شكرا على ها الشرح
You're very welcome.
Thank you
You're welcome 👍
Im trying to follow this. I still can't figure out the problem. I don't have spaces so i don't know what to do.
Hi computegaga how to be possible in active x control combo box
I just made a video of creating dependent lists with combo boxes on a userform - ua-cam.com/video/3VTq_kyCJec/v-deo.html
I do not have one for combo boxes on a worksheet though.
Thank you for this video.
You're welcome Aritra.
I have a bit different question… i read many comments but nobody has my issue, when i close my file and reopen it the list disappears, so i have to recreate it again, any solution please
And thank you so much
I'm sorry, this should not be happening. I have not experienced this before.
Hi!! I'm looking for a solution to my problem:- I want to have a drop down list with country names appearing in it, however when any of the country name is selected in drop down, it should automatically show the value of country code, which is on the adjacent column of country name.
This would be a lookup, Ankit. Lookup up the value of the country - ua-cam.com/video/qZ1ybnAXprk/v-deo.html
Thank you very much :D
You're welcome!
Thank you so much
You're welcome, Sandeep.
very helpful
Awesome! I'm glad it helped.
How to create college students portal.message to students parents abut result and other info
I don't have any material on this Sajid.
Finally found my solution to multiple text in the string on Internet
Great!
Amazing thank you
No problem 😊
Nice. Very helpful when list items contains spaces. I wish if something like this could work for strings starting with special characters and numbers also. I've entries like "1abc", "@Comm" in my first drop down list on the basis of which I need to filter elements in second drop down. I've heard there is some concept of aliases while naming cell ranges but I'm still exploring if that is really a possibility which can be helpful in this case.
You can hopefully use a similar technique to the video Rasik.
The cell would contain the special characters, but the name would use an alternative word maybe. You can then substitute the name for the symbol just like we did for the spaces.
Thank you Very Much, very helpfully
My pleasure Ahmad.
So I tried this and found that the second dropdown list does not show up. I tried recreating the second list but still running into the same issue. Any suggestions? I also can't seem to re-name lists in that first box at the top, it stays as the Cell(B2) no matter how I enter the list name.
Make sure you press Enter after typing the name. Clicking someone does not register it.
@@Computergaga Tried that and it still doesn't stay. Is there a way for me to do "indirect" to a cell on my list page? while still like list names with spaces
The cell can contain spaces, but not the named range. This video shows you how to get around this issue - ua-cam.com/video/aidOWOsMdD8/v-deo.html
@@Computergaga I guess the issue now is that I can't seem to change the named range at all, even when I have no space and press enter. It's really strange cause all other guides do what you do but for some reason, my excel doesn't seem to do that. I will try starting from scratch and see what I have missed.
@@kritikakitumarkin5664 you can solve your problem or not? I have same problem with you
If you choose something from the 1st dropdown list you get the correct list in the 2nd dropdown list . But if you choose some thing from 2nd dropdwon list and you changed your mind in the 1st dropdown list ... the 2nd dropdown list will not clear out or reset ad keep the old chosen value ... how to get around this
We would probably need a macro running on the change event of the cell(s) from the first drop down. And if it changed, clear what is in dropdown 2.
Computergaga thanks a lot
You're welcome Siyamand.
Thank you soooo muchhhhh.......
Most welcome, Subham 😊
Thanks
You're welcome
tq very much.at i can solve my dependant problem in excel
You are most welcome.
Excel me data validation problem
The list source must be a delimited list; or a reference to single row or Column
Mere me te likh raha hai aur kam nhi kar raha hai please help
Hey guys, I have done what was on the video but some lists still doesn't pull up...
The named area and list item will need to match exactly.
Hi, I'm probably doing something wrong. I tried to do the exact same thing in excel but when I enter the SUBSTITUE formula it says the formula isn't right. =indirect(substitute(A2," ",""))
I would need to see the specific example. Best to go through the steps again and see what might be mistaken.
I managed to make it work but how do I apply this to the entire column? I am struggling here.
That shouldn't be a problem. Select the entire column and then in the formula reference row 1. For example B1. Even those this is a header and not a list in that cell, it needs to start here if you select the whole column.
million thank your value clip :)
You're welcome Nanta.
Well done!
Thank you Angelo.
OMG THANK YOU FOR POSTING THIS. My issue was fixed by getting rid of spaces in the words e.g. SOUTH_AFRICA instead of SOUTH AFRICA
Great to hear 👍 My pleasure.
Very good
Thank you Sajid.
THANKS A TONNNNNNNNNNNNNN
No worries, Pooja.
This is not working still please check the formula is correct? =INDIRECT(SUBSTITUTE(F5," ",""))
The video formula is correct, there must be a mistake somewhere.
Are u able to help me with a dependent drop down query.if you can provide me with an email I can check with u
It does NOT work
Bar none one of the most frustrating efforts ever. Excel is sometimes a colossal waste of time. The infamous "There is a problem with this formula. Not trying to type a formula?" error message will have you screaming. Even the MS Help doesn't know how to make this work once that message appears. MS VBA = lame. Sorry this simply doesn't work.
Thanks so much
You're welcome, Uriel.