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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive