Monday, September 5, 2011

yogi_Make Validation List Of A Cell Dependent On The Validation In Another Cell

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
WvdR said:
How do I make the validation list of one cell variable dependent on the validation in another cell
I'm using Google Docs Spreadsheet and have the following problem.
I'm trying to put together a logfile for my team in which I have 5 main categories and several subcategories of issues.
So for example:
A1: Categorie
A2: either job unfinished, job done, job delayed, job cancelled.
I used the 'validate data' function with a list to create a drop down menu.
B1: Cause (sub-categorie)
B2: depends on what I choose in A2. E.g. when I choose job unfinished, B2 should have a value of 'technical error, human error, deprioritised, other', whereas when A2 would have been 'job cancelled', B2 should have a value of 'customer's request, company's decision, other).
Right now my B2 cell has a drop down list consisting of all the different subcategories, but I would like to make the validation list dependent on my choice in A2.
If anyone has any suggestions, that would be great! Thank you very much in advance, appreciate it a lot!
---------------------------------------------
In my proposed solution, I have used Data Validation as well as Named Ranges as delineated in the spreadsheet

7 comments:

  1. Hi Yogi - I have a similar request, but having a problem with your solution. I've created the drop down lists with the array formula, but once I go to the second line to select a new category, the sub category defaults to the first row selections. I can see the array formula is not updating per my selection on row 2 - any suggestions on how to fix this?

    ReplyDelete
  2. Hi Yogi,

    same here similar problem.
    What i would like to achiev is having the output of
    "=arrayformula(indirect(A2))"

    not in a validation list, but in a validation DropDown Menue again.

    What i didn't get is
    "DataValidation cell B2: Items from a list ... C2:C5"
    would i not have only a fixed range there and would NOT be able to choose different references for that "DropDown List"?(If that was the intention here?)

    ReplyDelete
  3. Ah ... some how I had missed seeing mtgoode comment. Sorry about that mtgoode!

    Now in regard to the comment both from mtgoode and kinngrimm, the solution as posted works the way it should.

    If you want to Create A Dependent List Of Items Based On A List Of Items In A DropDown List you may want to look at my following blog post ...
    yogi_Create A Dependent List Of Items Based On A List Of Items In A DropDown List

    Cheers!
    Yogi

    ReplyDelete
  4. Hi mtgoode:
    Hi kinngrimm:

    I think you may find my today's post on ... Create A Dependent DropDown List Of Items Based On A List Of Items In A DropDown List (WOW ... a mouthful that sounds confusing to me!) to be of some interest:
    yogi_Create A Dependent DropDown List Of Items Based On A List Of Items In A DropDown List

    Cheers!
    Yogi

    ReplyDelete
  5. Hi yogia, I think you're a genius. My example was much more sophisticated but I first copied your example (to the very last detail) and then just applied it to my situation. Thank you for your help!

    ReplyDelete
  6. Hmm, unfortunately, I was too quick with praising you. Your solution only works if a person has one item on a list. A "list" normally suggests presence of several items, maybe hundreds or even thousands. The cells A2 and B2 will work but now try to copy the formulas further down. Will it work for cells A3 and B3? No. Will it work for cells A100 and B100? No.

    ReplyDelete
  7. Hi domain-admin ... sorry sorry take back ;)?

    ReplyDelete