+ Reply to Thread
Results 1 to 4 of 4

Multiple dependent Data validation lists

  1. #1
    Registered User
    Join Date
    01-24-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel for Mac 2011
    Posts
    13

    Multiple dependent Data validation lists

    Hello All,

    Hopefully this is an easy one but I can't find an explanation anywhere on the net.

    The easiest way to explain this is with an example.

    On my input worksheet I have these headings:
    A1 - Main Category

    B1 - Cost Centre

    C1 - Sub-Category


    On my lists sheet I have:

    Main Category
    Column A
    A1 - Education
    A2 - Farming
    Etc.

    Cost Centre
    Column B - Education
    B1 - School 1
    B2 - School 2
    Etc.

    Column C - Farming
    C1 - Farm 1
    C2 - Farm 2
    Etc

    Sub-Category (This is where it starts to get tricky)

    Column D - Education
    E1 - Books
    E2 - Paper
    Etc

    Column E - Farming
    D1 - Clearing
    D2 - Planting
    Etc


    So ideally the sub-category data validation list would be driven by "Farming" or "Education" as selected in "Main Category" on the input page. However, the named ranges "Farming" and "Education" have already been used to drive cost centre.

    The problem is that all the farms share the same sub-categories, as do the schools.

    Any advice on how to make it work properly would be much appreciated.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Multiple dependent Data validation lists

    This might prove useful

    www.contextures.com/xlDataVal02.html
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    01-24-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel for Mac 2011
    Posts
    13

    Re: Multiple dependent Data validation lists

    Hi,

    Thanks for your quick reply.

    I've had a look at the link you posted and I don't see quite how that could solve the issue.

    Simply put, I need the choice from "Main Category" to drive dropdown lists for both "Cost-centre" and "sub-category".

    So if "farming" is selected then the "Cost centre" dropdown shows the farming cost centres and the "sub-category" dropdown show the farming sub-categories.

    Thank you again for your input.

  4. #4
    Registered User
    Join Date
    01-24-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel for Mac 2011
    Posts
    13

    Re: Multiple dependent Data validation lists

    I've put together a simple workbook illustrating the problem in case anyone can help. I've shaded the cells which I can't work out.

    Thank you

    Example Workbook.xlsx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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