Wednesday, July 3, 2013

Google Spreadsheet Multi Sub Drop Down List

Question:

( by Robert )

Hi I, want to create a dropdown list in google drive excel. 
The normal list is not a problem, but i have second dropdown that depends on the choice of the first dropdown list.

for example

i have a list of several items:

meat            liquid

horse           beer
dog              water
chicken        oliveoil
cow             wine



the first dropdown will show me the choice meat / liquid 
and if i choose meat i want in the second dropdown  the list with horse, dog, chicken ,cow.

How is this possible in google drive ? I know how it works under ms excel - but the way it works there does not work here.

Please help.

Solution:

Now to have dropdown list depending on another dropdown list in Google Spreadsheet, you have to do a work around.

Following Example will show you how to achieve this.

Let's have a Sheet 'Main Sheet', and in that sheet we have a dropdown in Cell B1. Have a look at the following screenshot:


We have validated the data from the list, that is 'DropDown List'!A1:C1

And following is the screenshot of Sheet 'DropDown List':


In Sheet ''DropDown List' as you can see, we have the data (which is to be selected in 'Main Sheet' in Cell B1). So user can select anyone from A1:C1. Now we have to filter that column's data that user selects in Cell B1 of Main Sheet. So insert the following formula in Cell E1 of Sheet 'DropDown List':

=arrayformula(if('Main Sheet'!B1=A1;A3:A6;if('Main Sheet'!B1=B1;B3:B6;if('Main Sheet'!B1=C1;C3:C6;""))))




So, now if user select "liquid" in Cell B1 of Main Sheet then Column E of 'DropDown List' will be populated by B3:B6

And now is the main important part of this tutorial. Now in Cell A2 of Sheet 'Main Sheet' you can validate the data from 'DropDown List'!E1:E100
So you will be now having a dropdown list based on another dropdown list. Have a look at the screenshot below:



Now if you change the data in Cell B1, then the contents of data validation of Cell B2 will automatically change. Have a look at the following screenshot of Sheet 'Main Sheet':




I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.
I also take up private and confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

38 comments:

  1. hello Kishan, and thanx for sharing ur knowledge about g.DOCS
    Anyway i've a question, U make only one drop down box and sub cell, but if there has many (maybe 500 or 1000) cells connected with sub cell dropdown box, how can i make these things?

    ReplyDelete
    Replies
    1. Kim - This can be accomplished using a temp sheet. I mocked it up here:
      https://docs.google.com/a/lonbinder.com/spreadsheets/d/11qmi61TALyYStU6ppHxoMi7UhF9NjGxz-R5dhY47Rg8/edit#gid=0

      Delete
    2. @Lon your mock-up did the trick for me.

      Delete
    3. @Lon this is def what I need I just don't understand it (sooo not a techie person!!)

      Delete
  2. I would also like to know how I could do this for more than 1 cell so when I drag the sub cell (B2) down or across, it will apply to the rest of the similar sub cells. The only way I figured out was to change the data validation range for each of the sub cells but that would take forever if I have hundreds of data. Thank you.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Sam - This can be accomplished using a temp sheet. I mocked it up here:
      https://docs.google.com/a/lonbinder.com/spreadsheets/d/11qmi61TALyYStU6ppHxoMi7UhF9NjGxz-R5dhY47Rg8/edit#gid=0

      Delete
  3. Thanks for the post. It's very helpful!
    I've looked through your other posts and haven't seen anything on making a listbox type solution to select multiple answers from the list at once. Is this possible?

    ReplyDelete
  4. Hi, I used this method and it worked flawless... But I have a problem.

    I´m trying to do this on multiple cels at the same time, the way you purposed can only be used for one menu and sub menu set.

    On my spreadsheet I have multiple sheets and each one of them have about 60 cells and sub cells wich I use drop down menus.

    Any ideas? I can send you a print screen of the spreadsheet....

    Thanks for the help anyway!

    ReplyDelete
    Replies
    1. Lucas - This can be accomplished using a temp sheet. I mocked it up here:
      https://docs.google.com/a/lonbinder.com/spreadsheets/d/11qmi61TALyYStU6ppHxoMi7UhF9NjGxz-R5dhY47Rg8/edit#gid=0

      Delete
  5. Thanks a lot for this cause someone else was looking for something similar. I modified your formula so when a teacher is chosen in the dropdown list a list of students will pop up on the cell containg your formula. Below is my sheet...

    https://docs.google.com/spreadsheet/ccc?key=0AtSDHUV4OUOIdHF4RjlGbUtKcE82ZnhMSkZLbTFyd3c&usp=sharing

    ReplyDelete
    Replies
    1. I need to use the same function as you do for when you choose a name, the drop down list appears. I tried formula and it wasnt working.. can you give me steps on how to do this? Thank you.

      Delete
    2. Did you look at my sample sheet? There is an added sheet called "Drop Down List". On the first sheet and the second sheet are "Comments" in some cells, they are noted with a little orance triangle in the top-right corner. If you hover you will see the comment, but to see the whole comment click on that cell. I will update the sheet and explain on that what I did and how the formula works. Just copy the link in above post and paste it in your browser, that should open the document and you will see the changes I made. Give me a day to update it and I will have it ready.

      mreighties :)

      Delete
  6. Sir,how to use a dropdown in case of multiple cells in case of large data which I have posted at > https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/O5oKD2Afkp0

    And Spreadsheet >

    https://docs.google.com/spreadsheets/d/1ZLVAUnky2FJ2XzyABnSzRygDENFPh0-cbBuxw4_rb5c/edit?usp=sharing

    Hope you could solve this issue also

    ReplyDelete
    Replies
    1. Suresh - This can be accomplished using a temp sheet. I mocked it up here:
      https://docs.google.com/a/lonbinder.com/spreadsheets/d/11qmi61TALyYStU6ppHxoMi7UhF9NjGxz-R5dhY47Rg8/edit#gid=0

      Delete
    2. hi,
      I am able get the solution through excel vba code for dynamic multiple dropdownlist but could not able to get any script/solution in google spreadsheet.Kindly refer the link >>
      https://groups.google.com/a/googleproductforums.com/d/msgid/docs/6f084291-d24a-48d6-9230-63aeeb0c7218%40googleproductforums.com

      If anyone could convert the code into google spreadsheet script then it would be very easy.Here I have three multiple dropdown list each one connected with each other dynamically.The sample link to the spreadsheet is
      https://docs.google.com/spreadsheets/d/1gSo8QuKRonj1q3JFRnIAEMgeMlJEamhznxYedBHsUmQ/edit?usp=sharing

      Hope anyone could solve this issue.

      Delete
  7. Great work. Not sure how I found this tutorial, but I did. Thank you.

    ReplyDelete
  8. Hi Lon! In the MainSheet how can I validate data one time for all the rows looking up to de dynamic arrays it creates in the _dropDropDownSelections list, without having to go cell by cell right clicking | Data Validation | typing the _dropDownSelections!: ?

    ReplyDelete
    Replies
    1. Hi Filipe. You could either copy/paste manually (yuck!) or you could use the "Validation+" script. I've added that to the google sheet for you to check out.

      Delete
    2. Hi Lon, I used the script. But when I use it, I get the following error: "The data validation rule argument is invalid". Any help will be appreciated!

      Delete
    3. I just figured that out! Need to remove the '-' from the sheet name. Works fine now. Thanks!

      Delete
    4. I just found this now but can't seem to find the "Validation+" script you are referring to.

      Delete
  9. Hi Kishan!

    Thanks a lot as well for such a little great tutorial--just what i was looking for!

    I still have a question:

    Your formula fills in the cells with data available in the same sheet ("F6 - Reds").

    What must i add into the formula so it gets data from cells in a different sheet ("F3 - Blues", for example)?

    =arrayformula(if('F6 - Reds’!E3=D20;I20;if('F6 - Reds’!E3=D21;I21;if('F6 - Reds’!E3=D22;I22;""))))

    ReplyDelete
  10. I think you need to change this:
    "And now is the main important part of this tutorial. Now in Cell A2 of"
    to this:
    "And now is the main important part of this tutorial. Now in Cell B2 of"

    ReplyDelete
  11. Hi,

    I have some suggestions:

    1. Sheet names don't show up in the pictures. You could replace the pictures with new ones that show the name of the sheet. You could move sheet name up in Photoshop if needed.

    2. For one of the sheets you used the name "DropDown List" and you are referring to it as "DropDown List Sheet". For the other sheet you used the name "Main Sheet". That means to be consistent, you would need to refer it as "Main Sheet Sheet". I recommend the following:

    Replace "Main Sheet" with "Survey".
    Replace "DropDown List" with "Admin Stuff".

    ReplyDelete
  12. How can I restrict the number of responses per drop down item. For example, if I have in cell drop downs of appetizer, entree and dessert, and I want only 10 people to sign up for dessert, how would I do that?

    ReplyDelete
  13. How can I restrict the number of responses per drop down item. For example, if I have in cell drop downs of appetizer, entree and dessert, and I want only 10 people to sign up for dessert, how would I do that?

    ReplyDelete
  14. In the above example I am getting values fine upto the step where E1 is populated based on B1. But when I try to add validation for B2, it picks up the formula From E1 instead actual values. What am I doing wrong?

    ReplyDelete
    Replies
    1. Never Mind. I figured it out. The format had to changed to 'plain text'

      Delete
  15. But how do you cause the 2nd dropdown to go blank when the first drop down is changed to prevent a validation error?

    ReplyDelete
  16. Unfortunately, too many people were in the example spreadsheet, made changes, and now it is unreadable. If you have a copy of the original, please post it up. I would love to learn how to do this with multiple columns. Thanks!

    ReplyDelete
    Replies
    1. @Unknown - I've restored the sheet back to the original, working version.

      Delete
  17. Hi.. I dind't find the script "Validation+". Is that still working?

    ReplyDelete
  18. Sir,

    Where is the Validation+ script and how to use it?
    I am unable to find, please let us know.

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. hello,
    i could really use some help. I am using data but ranges a:k rather than just a:c , im getting an error message.

    this is the formula im entering:
    =arrayformula(if(GUESS!E3=A1;A3:A15;if(GUESS!E3=B1;B3:B9;if(GUESS!E3=C1;C3:C10;if(GUESS!E3=D1;D3:D9;if(GUESS!E3=E1;E3:E6;if(GUESS!E3=F1;F3:F6;if(GUESS!E3=G1;G3:G8;if(GUESS!E3=H1;H2:H10;if(GUESS!E3=I1;I3:I8;if(GUESS!E3=J1;J3:J8;if(GUESS!E3=K1;K3:K13""))))))))))))

    ReplyDelete