Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
user Sathya Charana S.M. said:
If I compare with Microsoft Excel, we do NAME for the "Ranges Names" in Google Docs.
But when I am giving that particular "Range Names" at the time of using formula, I am getting error in formula.
Do you have any video to help to understand this issue or resolve this issue. I need to do a dropdown list which select from particular range of data as you have mentioned. But I am explaining it again below.
My selection would be like If I select "Fruit" from a dropdown list, it should show only fruits listed and if vegetables, it should show only vegetables and for Others, the others. May be my table of contents are like below.
Fruits
Orange
Grape
Apple
Mango
Vegetable
Onion
Tomato
Beans
Carrot
Others
Tamarind
Chilly
Salt
Pepper
Something like above will be my table.
please help me in this regard. You have already explained it. But I am unable to catch in that context. If you can give me editable file, I may understand, more. Because I need to see the Validation Window, what the options you have used. Even the Screenshots are ok for me.
-------------------------------
in the following is one solution to the problem
user Sathya Charana S.M. said:
If I compare with Microsoft Excel, we do NAME for the "Ranges Names" in Google Docs.
But when I am giving that particular "Range Names" at the time of using formula, I am getting error in formula.
Do you have any video to help to understand this issue or resolve this issue. I need to do a dropdown list which select from particular range of data as you have mentioned. But I am explaining it again below.
My selection would be like If I select "Fruit" from a dropdown list, it should show only fruits listed and if vegetables, it should show only vegetables and for Others, the others. May be my table of contents are like below.
Fruits
Orange
Grape
Apple
Mango
Vegetable
Onion
Tomato
Beans
Carrot
Others
Tamarind
Chilly
Salt
Pepper
Something like above will be my table.
please help me in this regard. You have already explained it. But I am unable to catch in that context. If you can give me editable file, I may understand, more. Because I need to see the Validation Window, what the options you have used. Even the Screenshots are ok for me.
-------------------------------
in the following is one solution to the problem
ok understood this and i was able to replicate it. Still i am not quite there.
ReplyDeleteWhen you look at A and B, i have there in each column several Dropdown menu. Where A-Column Dropdown menues are always the same but the B-Column Dropdown menus are depending on what was set in the coralating A-Column Dropdown.
And as the rows may expand more or less endlessly i need a way to identify for each row again what was set in A2,A3,A4 ... and depending on that choose a dropdown menu for B2,B3,B4 ...
as this atm only counts for one drop down at a time in A2 and B2, which arent expendable i am stuck.
i hope to find out a way to read out
"look at column A, the value 'which changes' in any of Column A cells gets returned" and thereby the coresponding cell in column B would get the correct values in their menu to choose from.
farewell
kinngrimm
I am having the same problem.
ReplyDeleteIf I have
=ARRAYFORMULA(INDIRECT("ColB"))
=CONTINUE(J21, 2, 1)
=CONTINUE(J21, 3, 1)
and so on below.
I tried manually changing it in each cell to:
=ARRAYFORMULA(INDIRECT("ColB"))
=CONTINUE(J22, 2, 1)
=CONTINUE(J23, 3, 1)
But that doesn't work.
So I need each column to call from the left not from row 21 (which is my top row in this case).
thanks,
c
Hi c:
ReplyDeleteI suggest you share your spreadsheet in Google Docs Help Forum, provide a brief description of your project, tell us what you are trying to accomplish, show us what is your expected result along with needed logic/explanation as to why that is the correct result ... it will help clearly understand what you are trying to accomplish.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
I am suffering from the same issue that kinngrimm is describing. Was anyone able to come up with a solution for this? I have an example here if that helps... https://docs.google.com/spreadsheet/ccc?key=0AisB_l341B_WdEJSOUtWNTdfRTdFQzZaOVozbmwzWlE&hl=en_US#gid=2
ReplyDeleteHi Brian:
ReplyDeleteI believe I have looked at your post in Google Help Forum. The problem is that in Google spreadsheet drop-down list (data validation) must be based on explicit reference to a range of cells.
If you can provide more details in your post on Google Help Forum, perhaps we can discuss via the chat window in your spreadsheet about whether we can or can not do what you are intending to accomplish.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi Yogi,
ReplyDeleteWe need sort of an extension of your solution. Wondering if you or someone here can help me?
We have a list of items called Processes. Each Process in this list has a sublist of Operations.
Now the Process list is shown in hundreds of cells as dropdowns and based on what is selected as a Process there are hundreds of corresponding sublists of Operations that the user needs to select.
Using your method we would have create hundreds of list lookups, you understand, which would be really really time consuming.
Is there any way we can do this dynamically? Is there a way that each of the sublist cells has to load the Data Validation from its list dynamically based on what the chosen Process is for that cell.
Hope you can help me!
Thanks a lot!
Do you have a doc that is editable that I can copy? your explanation is not broken in a more simplistic way for me. I cannot follow it. If I had a copy of an editable doc, or a way to view one share on line that would make it much easier.
ReplyDeleteHi Aaron:
ReplyDeleteI can't say whether your referring to my spreadsheet will help or not ...
but if you send me a request to share the following spreadsheet:
yogi_Create A Dependent List Of Items Based On A List Of Items In A DropDown List
I will be glad to do that.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
This comment has been removed by the author.
ReplyDeleteHi Aaron:
ReplyDeleteIt is hard to say from your description whether what you want to accomplish is doable. It will help if you share your spreadsheet ... for free technical support, please post your question in Google Docs User Forum sharing your spreadsheet with all relevant details and in case you do not want to share your spreadsheet in a public forum, contact me via vCita.com/yogi.anand
and then let us take it from there.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
I know how to do data validation. I am new to advanced formulas and arrays. I already have one list in column a: Loans, Vehicle, Miscellaneous, etc....
ReplyDeleteWhen I pick loans from the drop down in the column A, in column B i want these items to show up: Car loan, student loans, etc.... I want to this happen for multiple categories.
Essentially I need sub-categories to show in column B depending on what was selected in column A.
I cannot follow your example, as I have no experience doing this type of thing. I need a simpler guide that goes step 1 do this, step 2, do this. etc....