+ Reply to Thread
Results 1 to 6 of 6

Validtion list varying based on data on another cell

  1. #1
    Registered User
    Join Date
    02-21-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    9

    Validtion list varying based on data on another cell

    Hi,

    I have a validation list to be created on column C where only the list related to the value in Column B should be available to select from drop down list along with 1 default value 'UNKNOWN" in all drop down lists.
    As provided in the attached sheet, I have the actual data in Sheet1 and the list mapping in Sheet List. I Have provided only few of the records in Sheet "List" where there are 100 of records in my actual data.
    SO when the value under Item in Cell B2 is "Can", I should have the list related to value "Can" from Sheet "List" available in drop-down along with the value "UNKNOWN".
    The drop down list on Column C should be like below and I can select one form the list
    Tin
    Steel
    Opener
    Lid
    UNKNOWN

    Can anyone please let me know the steps to achieve this using the formulaes.
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Validtion list varying based on data on another cell

    Use the concept shown here: Dependent Lists

    See attached sample
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    02-21-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Validtion list varying based on data on another cell

    Thanks NBVC.

    But I have hundreds of values under Item column and to define name range for all of them would be very difficult.
    Is there any way to achieve this with out defining the name range for each value in Item field.

    Please suggest.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Validtion list varying based on data on another cell

    The main difficulty would then be adding the "Unknown" entry for each selection.... unless you insert a row in each group on the List sheet with an "unknown" entry....

    As is stands, you can name the whole list in the List sheet (column A:B) as MyList...

    Then for Validation in column B of Sheet1, use Validation|List and formula:

    =INDEX(INDEX(MyList,0,2),MATCH(B2,INDEX(MyList,0,1),0)):INDEX(INDEX(MyList,0,2),MATCH(2,1/(INDEX(MyList,0,1)=B2)))

    again, "Unknown" won't be included...

  5. #5
    Registered User
    Join Date
    02-21-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Validtion list varying based on data on another cell

    Thank you Very much NBVC.

    This works well after applying the above changes.But when I close and re-open the sheet,The drop down list do not appear.When I again goto Data Validation,Settings and click on OK it starts working and list is displayed.Do I need to goto Data validation settings and click Ok each time I open the sheet.

    Any help please would be higly appreciated.

    Regards
    SK

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Validtion list varying based on data on another cell

    I think it is because the part that defines the bottom of the required range is actually an array function...

    I tried this version and it was still there after closing and re-opening.

    Change the data validation formula for column C of Sheet1 to:

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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