dependent drop down lists on multiple lines (google sheets)

[chrisk67]

I have created a dependent drop down list in sheets based on a choice made in another drop down list, but as I am using arrayformula I cant see a way to have this work over 4000+ rows. It seems a static way to do something so will only work for the single row.

I would appreciate any pointers in the right direction

I have attached a sample/demo version of what I have done so far

[Pete_UK]

I assume that you want to add more subdivisions/regions down column D for other countries, so put 1 in cell C7 (as UK is first in your drop2 list), then 2 in C13 (as these relate to France, which is second in your list) and so on. Then in F2 you can use this regular formula:

=INDEX($D$7:$D$50,MATCH(MATCH($J$2,$E$2:$E$5,0),$C$7:$C$50,0)+ROWS($1:1)-1)&""

Copy this down as required. If you want to add data for Germany, put 3 in C18, and then the regions in D18 downwards.

Hope this helps.

Pete

[chrisk67]

Hi Pete
I dont think I explained myself very well. It is awkward using a demo sheet. The main one is for categorising and sub categorising work. each piece of work that comes in will be alocated one of each whoch would be entered at rows 21, 22, 23 etc using the drop downs created at I, J, K2. the problem is that without creating an array and drop down for each piece of work i am struggling. In the real worl I have 10 categories with between 3 and 10 subcategories.

so i would have job A category fred and sub category ginger
then job b with category lucille and sub category desi

etc
.

[Pete_UK]

Well here's a copy of your workbook. Can you see how it works within that?

Pete

[chrisk67]

hi Pete

I think you are quite a bit ahead of me. I can grasp the basics of what you have done, but will need to see how i can extrapolate to my real sheet and create the data validation to allow multiple drop downs.

thanks for the pointers.

Chris

[Pete_UK]

You're welcome, Chris.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

P.S. I'm in Manchester tomorrow - visit to the Dental Hospital (ugh!)

[chrisk67]

Thanks Pete

I have added to your ever growing reputation. Always fun at the dental hospital. I once had three wisdoms removed at once under general. easier to let them fall out.

Chris