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
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
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?
ReplyDeleteHi Yogi,
ReplyDeletesame 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?)
Ah ... some how I had missed seeing mtgoode comment. Sorry about that mtgoode!
ReplyDeleteNow 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
Hi mtgoode:
ReplyDeleteHi 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
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!
ReplyDeleteHmm, 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.
ReplyDeleteHi domain-admin ... sorry sorry take back ;)?
ReplyDelete