Problem Solved: Dependent Drop Down Lists in Excel

แชร์
ฝัง
  • เผยแพร่เมื่อ 10 ธ.ค. 2024

ความคิดเห็น • 183

  • @Gwytherinn
    @Gwytherinn 6 ปีที่แล้ว +4

    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!!!!

    • @Computergaga
      @Computergaga  6 ปีที่แล้ว +1

      You're welcome. Glad to be of help.

  • @krishnalawindz8443
    @krishnalawindz8443 4 ปีที่แล้ว +4

    EXCELLENT, ITS WORKED. I WASTED 2 DAYS OF MY TIME FOR SEARCHING SOLUTION. FINALLY I GOT HERE. THANKS A LOT TO YOU SIR

  • @sandrabotelho3287
    @sandrabotelho3287 4 ปีที่แล้ว +1

    YOU are so clear and helpful... tried so many videos.. none as clear as you thank you so much!

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว +1

      Glad it helped! Thank you for the comments Sandra.

  • @potatopancakehead793
    @potatopancakehead793 2 ปีที่แล้ว

    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!

    • @Computergaga
      @Computergaga  2 ปีที่แล้ว

      You're very welcome. Glad I could help.

  • @tomkochka2058
    @tomkochka2058 2 ปีที่แล้ว +1

    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!

  • @jessicaallen6342
    @jessicaallen6342 ปีที่แล้ว

    I have been struggling with this for 3 hours and finally found your explanation! Thank you for saving the remnants of my sanity!

    • @Computergaga
      @Computergaga  ปีที่แล้ว +1

      Glad it helped. Stay sane 👍😊

  • @jurijtsalyy5382
    @jurijtsalyy5382 2 ปีที่แล้ว

    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 ;)

    • @Computergaga
      @Computergaga  2 ปีที่แล้ว

      Thanks Jurij. Much appreciated.

  • @jamescosier5123
    @jamescosier5123 2 ปีที่แล้ว +1

    Hi, Do you have a video that allows you to do this "indirect" function on more than 1 column or data validation, please?

  • @marzhanshaimerdenova1688
    @marzhanshaimerdenova1688 2 ปีที่แล้ว

    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

  • @patrickschardt7724
    @patrickschardt7724 5 ปีที่แล้ว +1

    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.

  • @Therongunner
    @Therongunner 3 ปีที่แล้ว

    WOW!!! Saved my life! Thank you very much!!!

  • @glennmclagan1555
    @glennmclagan1555 4 ปีที่แล้ว +1

    Your tutorials are absolutely perfect. So easy to follow. Thanks for posting mate.

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      Thank you very much Glenn.

  • @kmalagar
    @kmalagar 2 ปีที่แล้ว

    This is what I was seeking… thanks

    • @Computergaga
      @Computergaga  2 ปีที่แล้ว

      You're welcome, Kim 👍

  • @noeldoyle2477
    @noeldoyle2477 5 ปีที่แล้ว +2

    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.

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว

      Sure. You can continue in the same vain. I have a video on this here - th-cam.com/video/5nb84p2wX-c/w-d-xo.html

  • @nikt7585
    @nikt7585 2 ปีที่แล้ว

    MVP of the century

  • @josephaloro766
    @josephaloro766 10 หลายเดือนก่อน

    @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.

  • @pusha6600
    @pusha6600 6 ปีที่แล้ว +2

    Thanx mate, was lookin to solve this problem for days. Thanx alot

  • @iNurtureEducation-fv5cj
    @iNurtureEducation-fv5cj ปีที่แล้ว

    Thank you for teaching best trick

  • @boffiee
    @boffiee 4 ปีที่แล้ว +2

    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
      @Computergaga  4 ปีที่แล้ว

      Thank you. It sounds like there is nothing in the first drop down cell.

    • @boffiee
      @boffiee 4 ปีที่แล้ว

      @@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?

    • @glydllnn
      @glydllnn 4 ปีที่แล้ว

      @@Computergaga THIS DOESNT HELP THE ISSUE.

  • @helenasvensson6747
    @helenasvensson6747 3 ปีที่แล้ว

    Fantastic functions and great tutorial! Thanks!!!

    • @Computergaga
      @Computergaga  3 ปีที่แล้ว

      You're very welcome, Helena. Thank you.

  • @juanfelipepatino7986
    @juanfelipepatino7986 2 ปีที่แล้ว

    Thank you!
    New subscriber here!

    • @Computergaga
      @Computergaga  2 ปีที่แล้ว

      Excellent! You're welcome.

  • @sruthiboddu8729
    @sruthiboddu8729 3 ปีที่แล้ว

    Thank you so much. Can you please tell how to do validation where special characters are present.

    • @Computergaga
      @Computergaga  3 ปีที่แล้ว

      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.

    • @sruthiboddu8729
      @sruthiboddu8729 3 ปีที่แล้ว

      @@Computergaga Thank you, but how do I put it in the formula if the actual column has both spaces and special characters ?
      Thanks

  • @prasoonsantoria8480
    @prasoonsantoria8480 3 ปีที่แล้ว

    This is super helpful! Thanks 😊

  • @goldylaxy4992
    @goldylaxy4992 6 ปีที่แล้ว +1

    simple but very effective, awesome work

  • @АСИ123
    @АСИ123 7 ปีที่แล้ว +1

    thanks a lot, but how can we create rows dependent on a drop down column list? is there a way to do it?

    • @Computergaga
      @Computergaga  7 ปีที่แล้ว +1

      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?

  • @MOATHALSHIBLIvfx
    @MOATHALSHIBLIvfx 2 ปีที่แล้ว

    شكرا على ها الشرح

  • @dcaweera
    @dcaweera 5 ปีที่แล้ว

    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

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว

      My pleasure Chathura. Glad to be able to help.

  • @LM-ty8ky
    @LM-ty8ky 3 ปีที่แล้ว +1

    Thank you

  • @deepafrancis5148
    @deepafrancis5148 5 ปีที่แล้ว +1

    Thank you for the video. It was indeed helpful. I have a query. What if the named ranges are seasonal_fruits and seasonal_vegetables?

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว

      Thanks Deepa. And the cell text was seasonal fruits and seasonal vegetables? Following the tutorial, replace the "" with "_".

    • @deepafrancis5148
      @deepafrancis5148 5 ปีที่แล้ว +1

      @@Computergaga yeah..I tried and it worked..thank you so much!

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว

      Excellent!

    • @deepafrancis5148
      @deepafrancis5148 5 ปีที่แล้ว

      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?

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว

      I don't know the answer to this Deepa.

  • @sahibzadaali4012
    @sahibzadaali4012 ปีที่แล้ว +1

    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.

    • @Computergaga
      @Computergaga  ปีที่แล้ว

      Try selecting something in the first list and check if the dependent one works.
      It may work despite this warning.

    • @lucionelucione
      @lucionelucione ปีที่แล้ว

      Sama problem it does not work @@Computergaga

  • @sergiosoares1921
    @sergiosoares1921 3 ปีที่แล้ว

    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

  • @TravisThomas1994
    @TravisThomas1994 ปีที่แล้ว

    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!

  • @tomasbejarano5981
    @tomasbejarano5981 4 ปีที่แล้ว +2

    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?

  • @naradaweerasooriya162
    @naradaweerasooriya162 4 ปีที่แล้ว +2

    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

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว +1

      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 - th-cam.com/video/sTj3KnbGFhM/w-d-xo.html
      can also be used to create dependent drop down lists.

  • @tamarasevyan9488
    @tamarasevyan9488 4 ปีที่แล้ว

    Very helpful video!!!!

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว +1

      Great to hear. Thank you Tamara.

    • @tamarasevyan9488
      @tamarasevyan9488 4 ปีที่แล้ว

      @@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

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      You would need a SUBSTITUTE function for each character to include.

  • @pollyyau1035
    @pollyyau1035 4 ปีที่แล้ว

    Thank you so much , it is fabulous! I have the problem for the list items contain brackets, how can i remove it, thank you!

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว +1

      Thank you, Polly. They can be removed with the same technique. You would need a SUBSTITUTE function for each bracket.

    • @pollyyau1035
      @pollyyau1035 4 ปีที่แล้ว

      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

  • @pratiksathaye7020
    @pratiksathaye7020 ปีที่แล้ว

    Thank you very much :D

  • @jmed5588
    @jmed5588 7 หลายเดือนก่อน

    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"

  • @waltermakuzander5248
    @waltermakuzander5248 4 ปีที่แล้ว

    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. 😊

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      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.

  • @evelynkalubi3994
    @evelynkalubi3994 ปีที่แล้ว

    Would this work for hypens or special characters?

    • @Computergaga
      @Computergaga  ปีที่แล้ว

      Sure! 👍 The same approach with SUBSTITUTE could replace any character that exists in a cell but not allowed in a defined name.

  • @DivaClassesMundawar
    @DivaClassesMundawar 3 ปีที่แล้ว

    Nice video 👍👍👍

  • @piercedarby2378
    @piercedarby2378 3 ปีที่แล้ว

    Hi could you help me with a problem on this

  • @sandeep19235
    @sandeep19235 3 ปีที่แล้ว

    Thank you so much

    • @Computergaga
      @Computergaga  3 ปีที่แล้ว

      You're welcome, Sandeep.

  • @caykgooryzs3063
    @caykgooryzs3063 3 ปีที่แล้ว

    tq very much.at i can solve my dependant problem in excel

  • @desapoyment-xt1dh
    @desapoyment-xt1dh ปีที่แล้ว

    very helpful

    • @Computergaga
      @Computergaga  ปีที่แล้ว

      Awesome! I'm glad it helped.

  • @aljunaidbaunto3808
    @aljunaidbaunto3808 2 ปีที่แล้ว

    How can u apply it to multiple row??

  • @vifetjoliMay
    @vifetjoliMay 3 ปีที่แล้ว

    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!

  • @Robert8455
    @Robert8455 5 ปีที่แล้ว +1

    Nice trick! Thank you Bud

  • @MrRegulator31
    @MrRegulator31 7 ปีที่แล้ว +1

    Awesome!!!! Thank you very much!!!!!!

  • @sunny7506
    @sunny7506 3 ปีที่แล้ว

    Awesome Due to Space issue in the top row list was reflected the error

  • @abbycao351
    @abbycao351 2 ปีที่แล้ว

    Finally found my solution to multiple text in the string on Internet

  • @femkezeilstra5030
    @femkezeilstra5030 7 ปีที่แล้ว +1

    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?

    • @femkezeilstra5030
      @femkezeilstra5030 7 ปีที่แล้ว

      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/

    • @Computergaga
      @Computergaga  7 ปีที่แล้ว

      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)

  • @Naturelover-yd7ex
    @Naturelover-yd7ex 3 ปีที่แล้ว

    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 "

  • @VijayKumar-zd7ou
    @VijayKumar-zd7ou 6 ปีที่แล้ว

    Thanx, but same condition work without VBA and not required userform. Only two combo box required for dyanmic range automatically select.

  • @namunyimbilinamposyachapew1144
    @namunyimbilinamposyachapew1144 3 ปีที่แล้ว

    Amazing thank you

  • @syukurshariff5853
    @syukurshariff5853 4 ปีที่แล้ว

    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"

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      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.

    • @syukurshariff5853
      @syukurshariff5853 4 ปีที่แล้ว

      @@Computergaga so i can just use the same code like urs for two words? or need a new one for the 4 words?

    • @syukurshariff5853
      @syukurshariff5853 4 ปีที่แล้ว

      i mean for the dependant drop down list

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      Same code as mine works for any number of words Syukur 👍

  • @eoinkirwan1635
    @eoinkirwan1635 5 ปีที่แล้ว

    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

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว

      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.

  • @AnonYmous-bt2vu
    @AnonYmous-bt2vu ปีที่แล้ว

    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.

  • @senorespena9237
    @senorespena9237 5 ปีที่แล้ว

    What if i have numbers, multiple words (more than 2) or special signs like &?

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว

      The same solution can be applied. You can handle the characters or the numbers the same way that we handle the spaces.

  • @manjit7715
    @manjit7715 4 ปีที่แล้ว

    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.

  • @SheridanWhiteside
    @SheridanWhiteside 5 ปีที่แล้ว

    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
      @Computergaga  5 ปีที่แล้ว

      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.

  • @Subhambiki
    @Subhambiki 3 ปีที่แล้ว

    Thank you soooo muchhhhh.......

    • @Computergaga
      @Computergaga  3 ปีที่แล้ว +1

      Most welcome, Subham 😊

  • @lynxwomancat
    @lynxwomancat 7 ปีที่แล้ว +2

    BRAVO!!!! Thanks!

  • @helloall3977
    @helloall3977 7 ปีที่แล้ว

    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.

    • @Computergaga
      @Computergaga  7 ปีที่แล้ว

      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.

  • @Hajora21
    @Hajora21 ปีที่แล้ว

    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

    • @Computergaga
      @Computergaga  ปีที่แล้ว

      I'm sorry, this should not be happening. I have not experienced this before.

  • @hth6777
    @hth6777 2 ปีที่แล้ว

    Thanks

  • @aridev009
    @aridev009 6 ปีที่แล้ว +1

    Thank you for this video.

  • @sajidrasheedkayani6541
    @sajidrasheedkayani6541 4 ปีที่แล้ว

    How to create college students portal.message to students parents abut result and other info

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      I don't have any material on this Sajid.

  • @joxphay
    @joxphay 5 ปีที่แล้ว

    Thank you Very Much, very helpfully

  • @ankitsaxena4488
    @ankitsaxena4488 3 ปีที่แล้ว

    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.

    • @Computergaga
      @Computergaga  3 ปีที่แล้ว

      This would be a lookup, Ankit. Lookup up the value of the country - th-cam.com/video/qZ1ybnAXprk/w-d-xo.html

  • @VijayKumar-zd7ou
    @VijayKumar-zd7ou 6 ปีที่แล้ว

    Hi computegaga how to be possible in active x control combo box

    • @Computergaga
      @Computergaga  6 ปีที่แล้ว

      I just made a video of creating dependent lists with combo boxes on a userform - th-cam.com/video/3VTq_kyCJec/w-d-xo.html
      I do not have one for combo boxes on a worksheet though.

  • @sajidrasheedkayani6541
    @sajidrasheedkayani6541 4 ปีที่แล้ว

    Very good

  • @RasikBihariTiwari
    @RasikBihariTiwari 7 ปีที่แล้ว

    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.

    • @Computergaga
      @Computergaga  7 ปีที่แล้ว

      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.

  • @lilyigwe1633
    @lilyigwe1633 4 ปีที่แล้ว

    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.

    • @lilyigwe1633
      @lilyigwe1633 4 ปีที่แล้ว

      I figured this out in the end. Thanks

    • @Tom-dj7lo
      @Tom-dj7lo 4 ปีที่แล้ว

      @@lilyigwe1633 I am facing this issue too, Can you tell me how to fix it ?

  • @shirishjirole9614
    @shirishjirole9614 4 ปีที่แล้ว

    Thank you very much. I was facing this issue and unable to find solution.

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      You are welcome Shirish. Thank you.

  • @chengg02
    @chengg02 4 ปีที่แล้ว

    Hey guys, I have done what was on the video but some lists still doesn't pull up...

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      The named area and list item will need to match exactly.

  • @kritikakitumarkin5664
    @kritikakitumarkin5664 6 ปีที่แล้ว

    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
      @Computergaga  6 ปีที่แล้ว

      Make sure you press Enter after typing the name. Clicking someone does not register it.

    • @kritikakitumarkin5664
      @kritikakitumarkin5664 6 ปีที่แล้ว

      @@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
      @Computergaga  6 ปีที่แล้ว

      The cell can contain spaces, but not the named range. This video shows you how to get around this issue - th-cam.com/video/aidOWOsMdD8/w-d-xo.html

    • @kritikakitumarkin5664
      @kritikakitumarkin5664 6 ปีที่แล้ว

      @@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.

    • @siamakalvand
      @siamakalvand 4 ปีที่แล้ว

      @@kritikakitumarkin5664 you can solve your problem or not? I have same problem with you

  • @poojasachdeva6314
    @poojasachdeva6314 3 ปีที่แล้ว

    THANKS A TONNNNNNNNNNNNNN

  • @vincentgrondman1357
    @vincentgrondman1357 4 ปีที่แล้ว

    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," ",""))

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      I would need to see the specific example. Best to go through the steps again and see what might be mistaken.

  • @nantaluangtongkum7806
    @nantaluangtongkum7806 6 ปีที่แล้ว +2

    million thank your value clip :)

  • @a101525
    @a101525 7 ปีที่แล้ว +1

    Well done!

  • @jojomama531
    @jojomama531 6 ปีที่แล้ว

    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

    • @Computergaga
      @Computergaga  6 ปีที่แล้ว +1

      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.

    • @jojomama531
      @jojomama531 6 ปีที่แล้ว +1

      Computergaga thanks a lot

    • @Computergaga
      @Computergaga  6 ปีที่แล้ว

      You're welcome Siyamand.

  • @abhiranjan8930
    @abhiranjan8930 ปีที่แล้ว

    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

  • @moisesc.8314
    @moisesc.8314 5 ปีที่แล้ว

    I managed to make it work but how do I apply this to the entire column? I am struggling here.

    • @Computergaga
      @Computergaga  5 ปีที่แล้ว +1

      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.

  • @vtam951
    @vtam951 4 ปีที่แล้ว

    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

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      Great to hear 👍 My pleasure.

  • @BaljeetSingh-ut3qf
    @BaljeetSingh-ut3qf 4 ปีที่แล้ว

    This is not working still please check the formula is correct? =INDIRECT(SUBSTITUTE(F5," ",""))

    • @Computergaga
      @Computergaga  4 ปีที่แล้ว

      The video formula is correct, there must be a mistake somewhere.

  • @irenesanjay
    @irenesanjay 4 ปีที่แล้ว

    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

  • @lucionelucione
    @lucionelucione ปีที่แล้ว

    It does NOT work

  • @ujean56
    @ujean56 3 ปีที่แล้ว

    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.

  • @urielwise1398
    @urielwise1398 3 ปีที่แล้ว

    Thanks so much