+ Reply to Thread
Results 1 to 7 of 7

dependent drop down lists on multiple lines (google sheets)

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    dependent drop down lists on multiple lines (google sheets)

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

    Re: dependent drop down lists on multiple lines (google sheets)

    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

  3. #3
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Re: dependent drop down lists on multiple lines (google sheets)

    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
    .

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

    Re: dependent drop down lists on multiple lines (google sheets)

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

    Pete
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Re: dependent drop down lists on multiple lines (google sheets)

    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

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,896

    Re: dependent drop down lists on multiple lines (google sheets)

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

  7. #7
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Re: dependent drop down lists on multiple lines (google sheets)

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiple Dependent Drop down lists to generate data
    By Acousticlife81 in forum Excel General
    Replies: 1
    Last Post: 04-20-2017, 03:58 PM
  2. [SOLVED] Help with multiple dependent drop down lists
    By NHamilton07 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2014, 07:58 AM
  3. Dependent drop down lists without creating unique named lists
    By pajordan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-11-2013, 12:20 PM
  4. [SOLVED] Data Validation using multiple dependent drop down lists
    By ceruppel in forum Excel General
    Replies: 4
    Last Post: 12-17-2012, 06:39 PM
  5. Defaults for Multiple Dependent Drop Down Lists in same Worksheet
    By KyleW in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2010, 03:39 PM
  6. Dependent Drop Down lists (multiple drop down)
    By jijy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2007, 09:56 AM
  7. Multiple Dependent Drop Down lists?
    By Steve in forum Excel General
    Replies: 0
    Last Post: 12-07-2005, 04:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1