Create Multiple Dependent Drop Down Lists

Share
Embed
  • Published on Sep 26, 2014
  • Create multiple dependent drop down lists in Excel. Each lists options are dependent upon the previous list selection.
    Learn loads more Excel skills - goo.gl/dZQhpg
    It is vital for your Excel reports that data is accurate. Assist users when entering data by providing drop down lists of the possible options. If you have large lists of options then break it down into several cascading drop down lists.
    To create multiple dependent drop down lists in Excel you will first need to set up your lists as named ranges. The Data Validation tool and the INDIRECT function can then be used to reference the named ranges creating dependent lists.
    Join the Audience:
    ★ Twitter: twitter.com/computergaga1
    ★Facebook: facebook.com/computergaga
    ★ Google + plus.google.com/+Computergaga
    View more awesome FREE Excel tips
    www.computergaga.com/blog/
  • Howto & StyleHowto & Style

Comments • 449

  • kaskej
    kaskej 4 days ago

    thanks it is very useful

  • Tran Ngoc Trung
    Tran Ngoc Trung 22 days ago

    Thanks for sharing

  • Max Gatsche
    Max Gatsche Month ago

    Great help. Regards

  • 2Activ Life
    2Activ Life Month ago

    Great help!!

  • Mahaboob Subhani
    Mahaboob Subhani Month ago

    Thank you for sharing this video. Quick question in regards to drop down list in data validation function, can we insert silcer? Reply will be appreciated thank you in advance

    • Computergaga
      Computergaga  Month ago

      You're welcome Mahaboob.

    • Mahaboob Subhani
      Mahaboob Subhani Month ago

      @Computergaga thank you so much for your response

    • Mahaboob Subhani
      Mahaboob Subhani Month ago +1

      @Computergaga thank you very much for your response

    • Computergaga
      Computergaga  Month ago

      You can use slicers with data formatted as a Table so I guess if the validation was in columns of a table yes. A Slicer is a filter so a different tool to Data Validation, so I'm unclear as to the context you would like to use it. But in theory they can work together sure.

  • sherwin sibbaluca
    sherwin sibbaluca Month ago +1

    thumbs up... one question... how to automatic clear the list of list 2 and list 3 if i changed the list of list 1? thank you.

    • Computergaga
      Computergaga  Month ago +1

      This would require a macro on the worksheet change event. So when content is cleared from list one it automatically clears others.

  • Azhar Elbi
    Azhar Elbi Month ago

    Thank you so much!

  • M AZEEM
    M AZEEM 2 months ago

    Thank You So Much Sir

  • Pavel Panfil
    Pavel Panfil 3 months ago

    Thank you very much. It was very helpful. Have a great day!

  • Alee Khan
    Alee Khan 3 months ago

    how can we create 3rd list based on car category and transmission
    e.g.
    toyota>automatic>Gli
    but i also have
    honda>automatic>civic
    also Gli comes with 3 colors i.e. black, white, sliver and also with 2 engines 1300cc and 1600cc how can we create 4 -5 lists based on this data

    • pavitra nanda
      pavitra nanda 3 months ago

      It is definitely possible to do.

    • pavitra nanda
      pavitra nanda 3 months ago

      ​@Alee Khan The "Tree algorithm" must be clear
      1) Toyota ->automatic/Non --automatic
      2) Automatic -> Car_type
      3)Car_type->Honda,Corolla,Civic
      4) Honda->Engine/colour Corolla->Engine/colour Civic- >Engine/colour
      5) Color -> Black,White,silver
      6)Engine->1600cc/1300cc and so on.

    • Alee Khan
      Alee Khan 3 months ago

      @pavitra nanda how
      aa we cannot make 2 lists under same name.
      .
      is there any other way.
      if i select automatic under toyota it show gli vitz
      and if i select automatic under honda it show civic and city

    • pavitra nanda
      pavitra nanda 3 months ago

      yes, it is possible

  • Tanushree Rakshit
    Tanushree Rakshit 3 months ago

    Superb .Have seen many Videos on multiple dependent drop down but they were sooooo complicated & confusing .Your explanation & presentation has made it crystal clear.Thank you so much for the video.Cant thank you enough.:)

  • Prabu Saul
    Prabu Saul 3 months ago

    BUT IF I USE SPACE, ITS NOT WORKING

    • Computergaga
      Computergaga  3 months ago

      I have a video with a solution for this - thexvid.com/video/aidOWOsMdD8/video.html

  • Tejashree Deokar
    Tejashree Deokar 3 months ago +1

    Thank you!

  • Tom Griffin
    Tom Griffin 3 months ago +1

    Thanks for this! Much easier to understand than other examples that I have seen around!

  • Selma Inan
    Selma Inan 3 months ago +1

    Thanks for help. It was really helpful.. Super explanation...

  • tayeb rizvi
    tayeb rizvi 3 months ago

    There is one loop whole i.e if we select value in column A then column B cell is not getting blank, I fact it retains the old value. The previous value of other category

  • Will Jones
    Will Jones 4 months ago +1

    You are an absolute hero. I know this is an old video but I want you to know that this brilliant work is still being used today. Thanks for your help

  • Shruti Suryanarayana
    Shruti Suryanarayana 4 months ago +1

    I wanted to create a very detailed expense chart but didn't know how to create dependent lists. Thanks to you, i did it in just a few minutes. The way you explain stuff is very good, even someone with very less excel knowledge can do it easily.

  • David Galloza
    David Galloza 4 months ago +1

    Bro, thank you, I've been looking for something like this for hours.

  • J. B. Wagler
    J. B. Wagler 5 months ago +1

    Thanks, this was a great help!!!

  • Adnan Bakdash
    Adnan Bakdash 5 months ago +1

    Thanks it was very helpful

  • Salah Aldeen
    Salah Aldeen 5 months ago +1

    great help, ta mate

    • Computergaga
      Computergaga  5 months ago

      You're very welcome, thank you Salah.

  • imen aoussaji
    imen aoussaji 5 months ago

    Hello
    Thank you very much for the video. It was super helpful! However, I am not able to name my list 2 as list 1 since the elements in List 1 have characters like space, / etc. for example: Media Costs. I would have to take off the space. So, the name box in excel rejects the name since it has forbidden characters. Do you know any solution for this?
    Thank you!
    Imen

    • Computergaga
      Computergaga  5 months ago

      Hi Imen, yes I have a solution to the space issue in this video - thexvid.com/video/aidOWOsMdD8/video.html

  • Leigh Baker
    Leigh Baker 5 months ago +1

    Very easy to follow along. Thanks

  • Sumat Sethi
    Sumat Sethi 5 months ago +1

    Putto phalo. Thank you so much

  • Pankaj Sharma
    Pankaj Sharma 5 months ago +1

    Nice way to explain..

  • Kevin Moonegan
    Kevin Moonegan 5 months ago

    Hi, how can we use drop down list to get a value. for example: drop down list done, i select 1 category(expense). result i want is THE AMOUNT IN NEXT CELL APPEARS AUTOMATICALLY

  • kabir750
    kabir750 5 months ago

    Fantastic video - when i choose new category in the firs list- Can I automatically hide the previous result in the second list? Because if I do not select ןמ the second list .then the first result remains

    • Computergaga
      Computergaga  5 months ago

      You would probably need a macro for this because you would need to test if the value in the first list has been changed or not, and a formula can't handle that. The Worksheet Change event can be used.

  • Mukesh Mishraa
    Mukesh Mishraa 6 months ago +1

    Exceptional

  • Awaking Beauty
    Awaking Beauty 6 months ago +1

    LOVE ! Thank you

  • Tejas Punjani
    Tejas Punjani 6 months ago +1

    Great efforts and well explained.. Keep it up.!

  • Vince Rivas
    Vince Rivas 6 months ago +1

    Very helpful video! I was personally experiencing an issue with a dependent list I had created, in which I had a bunch of blank cells after each list. Your demonstration on use of the name ranges in the indirect formula helped to resolve this. Much appreciated!

  • bpkingcobra
    bpkingcobra 6 months ago

    This was very helpful Thank you. I'm creating a similar list but I have a question...using your example, you have Books under Category...what if you also had Books in another column, Would it cause problems when naming your ranges? Also, how would naming ranges work if for example instead of Games you had Computer Games under Category? Do you have to use underscores to separate the words?

    • bpkingcobra
      bpkingcobra 6 months ago

      @Computergaga Thank you!

    • Computergaga
      Computergaga  6 months ago +1

      The named ranges would need to be unique, so you may need to be clever with the names. With the space issue, yes an underscore can be used to separate words. But you don't need the underscore in the list item, just the named range. You can replace it with the SUBSTITUTE function shown in this video to solve this problem - thexvid.com/video/aidOWOsMdD8/video.html

  • mohamed rashad
    mohamed rashad 6 months ago

    Thank you dear. but there is a problem when I modify an entered data in a main cell, the sub cell remain e.g. if the written data was (Books-Romance-elephant moon) and I modified books to films, Romance-elephant moon would remained. Any ideas for this problem? Thank You

  • EuReGme
    EuReGme 7 months ago +1

    this was a very helpful video. thank you so very very much.

  • flames 8889
    flames 8889 7 months ago +1

    This video saved my ass. Thanks, mate.

  • harshul seth
    harshul seth 8 months ago

    really appreciate it.. i was banging my head since a day just to remove $sign before numerics.
    what this $ sign in excel indicates?

    • Computergaga
      Computergaga  8 months ago

      In a formula it indicates an absolute address or reference. This is a cell reference that does not move when a formula is copied. I have a video on them here - thexvid.com/video/b2wcFAb0v8k/video.html

  • Muaathe Aziz
    Muaathe Aziz 8 months ago

    I have a question and I do not know if this job is possible or not by excel, a job is included a number of students they have to chose three options of supervisor and the report will include a competition between the students whose chose the same person according to their grade. Number the students for a supervisor will be just three so if there is 5 students chose the same person there will be two students will move to their next choice for another competition and so on. I do not know what is the name of such process and I hope excel can do it and advise will be appreciate

    • Muaathe Aziz
      Muaathe Aziz 7 months ago

      @Computergaga thank you so much any advice for an example pls

    • Computergaga
      Computergaga  8 months ago

      Excel could do this, but it would required some logical formulae to assign the correct choice per student. Not necessarily simple.

  • The Ascend Company
    The Ascend Company 8 months ago

    Great video, my only issue, I need to have items with spaces between them as name ranges, List 1 = Regions, contains Region 1, Region 2..... List 2 Region 1 "but can't do thas an error occurs when setting range name, is there a way around this? It works perfectly without​ spaces.

    • Computergaga
      Computergaga  8 months ago

      I have a video on the problem caused my spaces and how to solve it here - thexvid.com/video/aidOWOsMdD8/video.html

  • İsmail Tokmak
    İsmail Tokmak 9 months ago +1

    thx!

  • Jayce Ng
    Jayce Ng 9 months ago

    Hi! Thanks for this but I'm wondering if my 2nd lists are all the same selection e.g. positive, neutral and negative for different choices in list 1, and i want to extract data based on these 2 lists. How do i do that further on?

    • Computergaga
      Computergaga  9 months ago

      In the tutorial it uses the values from cells B1 and B2 in the formula. You would do the similar depending on your sheet layout and criteria. But using the cells of the two drop downs.

    • Jayce Ng
      Jayce Ng 9 months ago

      Thank you for your response but how do i combine this 2 dropdown with this other tutorial?

    • Computergaga
      Computergaga  9 months ago

      Sounds like you want a multiple column lookup for retrieve cell information related to selections in your first and second drop downs. This tutorial will show you how - www.computergaga.com/tips/lookup_formulas/multiple_condition_lookup_formula.html

  • Denny Guerrero
    Denny Guerrero 9 months ago +1

    Thank you! This is exactly what I need for a template I'm working on at my new job!

  • Kaneo -
    Kaneo - 9 months ago

    my data has only numbers, for ex: if the 2nd dropdown chose number 10 and then the 3rd dropdown will have options for "10" list. but it doesn't work :(

    • Computergaga
      Computergaga  9 months ago

      You cannot use numbers as named ranges. So we would have to be clever and maybe put an underscore before the named ranges such as _10 and _12.
      Then when someone chooses 10 from the list (pretend the list is in cell A2) the Data Validation rule could be =INDIRECT("_"&A2) to concatenate the unscore to the number selection to match the named range.

  • Chris Lewis
    Chris Lewis 10 months ago +1

    Super thanks

    • Computergaga
      Computergaga  10 months ago

      Not a problem Chris. You're welcome.

  • vanderley nhantumbo
    vanderley nhantumbo 10 months ago +1

    Thanks it worked perfectly

  • 올겐매니아
    올겐매니아 10 months ago +1

    Thanks for the explanation.

  • Rohit Parab
    Rohit Parab 10 months ago +1

    Thanks buddy, you helped a lot

  • Ignasius Bagus Satrio Suhardiyono

    Love this! Can't describe how it helps! Super thank you :D

  • SB
    SB 10 months ago +1

    Thank you

  • Steve Groot
    Steve Groot 10 months ago +1

    Thank you. Very easy to understand.

  • Rick Bailey
    Rick Bailey 11 months ago +1

    Super clear presentation, thank you!

  • Byju Lakshman
    Byju Lakshman 11 months ago

    hi chief.. would you please let me know how to increase the font size and length of the drop list. its become very difficult for me to search and select the items from the list because its shows very tiny letters. would be great if you could send me a link of the video. Thanks.

    • Computergaga
      Computergaga  11 months ago

      No problem my friend.

    • Byju Lakshman
      Byju Lakshman 11 months ago +1

      Thanks chief...

    • Computergaga
      Computergaga  11 months ago

      Unfortunately the font size and list length cannot be changed in a Data Validation list Byju.
      Debra Dalgleish from Contextures has written on it here, with a solution of using a Combo Box - www.contextures.com/xlDataVal08.html#Font - However this then detracts from the techniques shown in this video.

  • Nick Deknudt
    Nick Deknudt 11 months ago +1

    Merci, Bedankt, Thank you!

  • BManchego
    BManchego 11 months ago +1

    This is exactly what I needed. Appreciate the info!

  • krishna rathi
    krishna rathi 11 months ago

    What if the name range have space in between? It is not working....Can you please let me know asap

    • Computergaga
      Computergaga  11 months ago

      I have a video on solving that problem here - thexvid.com/video/aidOWOsMdD8/video.html

  • TheLincolnBlogs
    TheLincolnBlogs Year ago +1

    THANK YOU! SO EASY!

    • Computergaga
      Computergaga  Year ago

      Named ranges cannot start with numbers. But they can be used for example _1 would be allowed. Then you could use a technique similar to this video where we avoid the space issue - thexvid.com/video/aidOWOsMdD8/video.html

    • TheLincolnBlogs
      TheLincolnBlogs Year ago

      Haha. I spoke waaay too soon. Your example was easy to follow and it worked when I was following the steps as I followed along. However, I am unable to apply it to my workbook. Won't it work with numbers?

  • iLx
    iLx Year ago +1

    Best tutorial regarding the subject so far...Thanks a Lot!

  • Farah Haddad
    Farah Haddad Year ago

    Amazing video thanks, but I was looking at a way to do it without the housekeeping of the lists at the beginning because my last is really huge and has 6 levels

  • Vikash Sharma
    Vikash Sharma Year ago

    OMG! This was great! Thank you so much for this video. Just 1 question, I am not sure if its possible in excel. Can once cell have multiple selection? Meaning to say i can select multiple categories like Book & Films and then further select multiple categories from their sub levels. So i need multiple data in the same field which can be separated by a comma.

  • Kristian Villarente

    mines not working when go to list 2., i dont get it , when infact im following everystep
    ..:-(

  • Ri delRio
    Ri delRio Year ago +1

    Very helpful! I am taking a complex course on financials and this will help me break down my reference guides!!!

  • Ahmed Aziz
    Ahmed Aziz Year ago +1

    thanks alot

  • Mahendar Singh
    Mahendar Singh Year ago +1

    Great !!

  • Asim Khan
    Asim Khan Year ago

    I tried this method but I think my problem is that my dependent drop down list is in numbers. Column A has the names and Column B which is the dependent list has a 6 digit number cost center number which belongs to the person in A. Would you know if there is a workaround for this?

    • Asim Khan
      Asim Khan Year ago

      Thank you. That worked perfectly

    • Computergaga
      Computergaga  Year ago

      Hi Asim,
      You will want to format the cost centre number as text. There will be a problem with using numbers as a named range though. It is not allowed.
      As a workaround you could enter a underscore before the number such as _123456. Then when doing the INDIRECT for a dependent list substitute the underscore out so that the named range and cost centre number match.
      I have a video here solving the issue of no spaces allowed in named ranges when doing dependent lists - thexvid.com/video/aidOWOsMdD8/video.html
      Your situation is similar so you may benefit from it.

  • Bob Caine
    Bob Caine Year ago +1

    WOW! I would have never known that was possible. Thank You

  • Dawncyrik Caparas
    Dawncyrik Caparas Year ago +1

    OMG! I have struggled with this for quite some time! I've been working on my spreadsheet for 3 hours! you are amazing!

    • Computergaga
      Computergaga  Year ago

      Thank you very much Dawncyrik. You are most welcome.

  • David Morello
    David Morello Year ago

    Thanks. Very clear. Is there a way to clear out the 2nd list when you change the value of the first list? When I change the value of the first list I can see the new list in the drop down but the previously selected value is still in the cell.

    • Computergaga
      Computergaga  Year ago

      For it to automatically change when the cell is changed, you would need VBA.
      You can use an IF function in the dependent list criteria. So for example, we could clear the second list, if the first list is changed to blank.
      But if the first list is changed to another value. I cannot see how we could automatically detect this without VBA.

  • SD
    SD Year ago

    The issue I have is that you can't have a list name with spaces. So if the first option has two words i.e. Science Fiction I can't create a second dependant list. Any ideas?

    • Computergaga
      Computergaga  Year ago

      Sure. I have a video on it here - thexvid.com/video/aidOWOsMdD8/video.html

  • Joshua Katz
    Joshua Katz Year ago

    Can I make multiple lists based on column 1?
    For example, I have a restaurant which has 3 sizes of lunch options, a basic sandwich box, a plus box and a deluxe box. Each one adds options
    Box = sandwich choice, and drink choice
    Plus Box = sandwich choice, drink choice and dessert choice
    Deluxe box = sandwich choice, drink choice, dessert choice, salad choice
    So I need to be able to add a dessert choice only if plus or deluxe are selected and a salad only if the deluxe box is selected which would be selected in column 1.
    Thanks!

    • Computergaga
      Computergaga  Year ago

      Yes you could use IF functions in the Data validation rules too. So only show the Salads list if Deluxe is selected in column 1.
      I assume the choice for sandwiches, drinks and salads are all different lists, in different columns. Just some lists are conditional.

  • Mark Fleming
    Mark Fleming Year ago +1

    Great Video Sir! I have been struggling to get my list to work correctly and the "$" tip is probably the key! Learned 2 new things. Thanks for this video.

  • Person B
    Person B Year ago +1

    Great Stuff, Thank you!

  • Diego MM
    Diego MM Year ago

    Great video! Question: How do I make the range of my lists columns go from 2 to, say, infinity?

    • Computergaga
      Computergaga  Year ago

      Thanks Diego.
      With this method we just have to keep going with the setting up for names, and then entering the little formula in Data Validation.
      You could possibly set up a macro to automate the process. But really if you have a lot of list. A lot of set up.

  • Didier Castelijns
    Didier Castelijns Year ago +2

    4 years later, and still the best explanation!
    Sir I salute you, thanks for saving/helping!

    • Computergaga
      Computergaga  Year ago

      You're welcome Didier. Thank you for your comments.

  • Alexander Roxborough

    Is there a way for multiple dependencies?
    For example, I have set up a spreadsheet with:
    Site Address | Discipline (I.E. Plumbing etc.) | Item / Snag (Issues, like blocked toilet) | Assign to Contractor
    Each site has different contractors.
    The Item/Snag is dependant on the discipline selected
    But I can't figure out how to get the Assign to Contractor cell to pick the contractor depending on the site listed, the discipline selected and the Item/Snag. The idea is to automate whom to contact when something in our apartments breaks or fails etc...
    Do you have any ideas how to achieve this?

    • Computergaga
      Computergaga  Year ago +1

      Excellent work Alexander. Well done.

    • Alexander Roxborough
      Alexander Roxborough Year ago

      Computergaga managed to make it work using VLOOKUP and INDEXING with a macro that auto creates parameters and assigns an individual code to each variation of each site and each discipline etc! If you’d like to see it I’d gladly send it via email. Seems like a very powerful tool now. I got the basics down following this video so I wouldn’t have worked out the rest without your input. Thanks a lot!

    • Computergaga
      Computergaga  Year ago +1

      Got a little confused just reading through it. However it seem like an approach more suited for VBA. If you have many contractors/sites/disciplines it will make up a lot of lists.
      Vba can handle all the conditions in the background and work out a list of contractors to pick from based on the 3 requirements.

  • Dave Monaghan
    Dave Monaghan Year ago +1

    Actually understandable presentation very well explained and not assuming viewer has extensive experience with subject matter, like most other presentations on this subject. Thanks for help.

    • Computergaga
      Computergaga  Year ago

      You're welcome Dave. Thank you very much.

  • P Naranjo
    P Naranjo Year ago +1

    YOU ARE THE MAN!!! THANK YOU SO MUCH!!

  • Shrikant Sabale
    Shrikant Sabale Year ago +1

    Good presentation ..thanks

  • Sharon Ha
    Sharon Ha Year ago +1

    Thank you!

  • TheBantuKing
    TheBantuKing Year ago

    does this auto update though? so if I added another category later on, would it show?

    • Computergaga
      Computergaga  Year ago

      Absolutely. If the category is added to the category list, and a new column set up like in the tutorial.
      What I would suggest is to insert the new category as a cell in the middle of the category list so that the list expands. You can always then sort it to put it in order.

  • Hamad Yousef
    Hamad Yousef Year ago +1

    Thank you. that was very helpful

  • Michael Yacoub
    Michael Yacoub Year ago

    Hi, Do you know how to use a drop down list at a cell in case IF, for example, if I have two cells A&B, at cell A, I have "YES" or "NO" choice, then I want to use a drop down list at cell B in case of "YES" at cell A, Do you know who I can do that?

    • Computergaga
      Computergaga  Year ago +1

      You can write an IF function as the reference for a defined name. So you could use something like =IF(A1="yes",E1:E4,F1)
      E1:E4 contains some options for a list, and F1 is empty.
      Then refer to this named range for the Data Validation list.

  • Viktor Osas
    Viktor Osas Year ago +1

    So explicit! Now I feel great knowing how to do this that I've so desired to learn for a while now. Thank you so much!

  • Bulingtar Gaunpalika

    Very Helpful ...Thanks a Lot.............

  • Juliana Pinheiro
    Juliana Pinheiro Year ago

    Hello! Great video. One question: I need to do the conditional validation from a list of NUMBERS... When I type it, this message from Excel appears: "You must enter a valid reference you want to go to, or type a valid name for the selection" :(
    Isn't it possible to name the lists with numbers?? Thank you!!!!

    • Computergaga
      Computergaga  Year ago

      You're very welcome.

    • Juliana Pinheiro
      Juliana Pinheiro Year ago +1

      Thanks a lot !

    • Computergaga
      Computergaga  Year ago +1

      No I'm afraid not. You can use numbers in a named range, but you cannot begin within them. So you could use _1 as a name, but not 1.
      I have done a video on solving the problem that they do not allow spaces in a named range either - thexvid.com/video/aidOWOsMdD8/video.html
      You need a similar approach. Use things like _23 as the named range. In the list it would be 23. So the DV list could refer to something like =INDIRECT("_"&B2)

  • Bikas Singh
    Bikas Singh Year ago +1

    Thank you

  • Lucy Edge
    Lucy Edge Year ago +1

    Great how to video- very helpful. Thank you :-)

  • Nabir Hossain
    Nabir Hossain Year ago +1

    I follow your tutorial but I face this problem............
    The source currently evaluates to an error. Do you want to continue?
    now what can I do?

    • Computergaga
      Computergaga  Year ago

      I would suspect an inconsistency between the named range of the area, and the text entered in the previous cell. Check they are the same.

    • Vikas Soni
      Vikas Soni Year ago

      I have same problem and previous cell have data but this msg appear and dependant drop down not working. Plz help what am i do?

    • Computergaga
      Computergaga  Year ago

      Continue. It sounds fine. The would appear if there is no value in the cell that the list is dependent on. It will probably still work when you enter something in that cell.

  • Adelaida G
    Adelaida G Year ago +1

    What if the header names are more than one word?

    • Computergaga
      Computergaga  Year ago

      I have a video to solve this problem Adelaida - thexvid.com/video/aidOWOsMdD8/video.html

  • Pawan Tiwari
    Pawan Tiwari Year ago +1

    Thank you so much

  • ahmed salem
    ahmed salem Year ago +1

    very useful, thanks

  • Jiří Bém
    Jiří Bém Year ago

    INDIRECT function is very limited for having specific table name in the cell. And you can not have more same table names. If you wish to do it independent on the content of cell and name of dropdown option.
    E.g.: if you have checkbox (Yes / No option) and in next cell you want to have dropdown with Yes option or NO options. - There is limitation, you can not have more of this Yes / No tables...
    Simply use IF funcition.
    1. Create Name of Table via "Formulas/Name manager" - or use shortcut F4
    2. Use Data Validation and enter for e.g.: "=IF($A1="y",Cars,"")" - if in cel A1 is y, in edited cell will be dropdown option base on TableName "Cars"
    DONE
    If you need more option in one cell, just use more IF statements in each other... - or use makro with cycles.

  • Rasik Bihari
    Rasik Bihari Year ago +1

    Thank you. very nice explanation. I was looking something exactly like this. But it seems excel doesn't allow to give name of the range to begin with a number. That's a limitation for me :(. How can I accomplish it if I've some items in my first drop down list which start with a number?

    • Computergaga
      Computergaga  Year ago +1

      You are correct about this limitation. The only way around it is to create some clever workaround.
      I have a video here - thexvid.com/video/aidOWOsMdD8/video.html
      This video will solve the problem about not having spaces in a named range.
      We will need a similar approach which depends on how your data is structured. But maybe use a word, instead of number i.e. one instead of 1. Then do a formula like in the video with SUBSTITUTE but replacing 1 with one to change the cell value to named range for matching. You may need a lookup if you have lots of numbers.
      I hope this makes some sense.

  • Noe Garcia
    Noe Garcia Year ago

    Your explanation and instruction is on point, but for some reason it is not working for me.

  • junee amlag
    junee amlag Year ago

    any videos for names that are in 2 words?

    • Computergaga
      Computergaga  Year ago

      Yes, I have a video to answer that problem here - thexvid.com/video/aidOWOsMdD8/video.html

    • junee amlag
      junee amlag Year ago

      "you must enter a valid reference you want to go to, or type a valid name for this selection." what to do so tjat i can name it in two separate words??

  • amitnandan1
    amitnandan1 Year ago +1

    Excellent! Exactly what I was looking for. Thanks

  • ANANTA MONDAL
    ANANTA MONDAL Year ago +1

    thanking you .. you are awesome

  • johngordon chinonye

    Thanks. it helped alot
    i have a question? i noticed spaced words are not allowed in this drop down list. please is there a way i can include words with space e.g weight variance

    • Computergaga
      Computergaga  Year ago

      No spaces in named ranges so you would need to set this up without e.g. WeightVariance. On the spreadsheet you can write it with though.
      Then for the formula you could use INDIRECT(SUBSTITUTE(A2," ",""))

  • Jonathan Crotser
    Jonathan Crotser Year ago +1

    Worked right away

  • Tom Murray
    Tom Murray Year ago

    Could the lists be created as tables allowing them to be extended?

    • Computergaga
      Computergaga  Year ago

      Yes absolutely Tom. However, I think I am right in saying that you cannot reference the Table directly from Data Validation. So you may need to create a named range for it also.
      The Table would extend automatically as you say, and then so would the named range and your DV lists.
      Or you could use a formula in your named range like this video - thexvid.com/video/vhrYQxooPBk/video.html

  • Batchu Nagaswaroop

    Hi Sir, In your video at 3:20Min to 3:40 Sec iam not doing the s=changes for my data but it is not reflecting in the excel file. any settings need to be changed. Please update

    • Computergaga
      Computergaga  Year ago

      I am naming ranges in that point in the video. Just type the name such as Books and press Enter. You must press Enter, not just click somewhere. Some symbols are not allowed in the name.