Google Spreadsheet Post #1170
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 04, 2013
user StudentSupport (http://productforums.google.com/forum/?zx=489otlptm5fw#!mydiscussions/docs/OsltWNvu2Dg)
I am trying to pull data, but with user selected input. If a user decides they want to view data from a certain column on another sheet then they can put an "x" in a cell. I have a helper column that helps write the formula prior to pulling the particular information.
="select C, D, E, F, G, H where B1:B="x"", which gives me a parse error.
Please view: https://docs.google.com/
----------------------------------------------------------------------------------------------
following is a solution based on my understanding of the problem
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 04, 2013
user StudentSupport (http://productforums.google.com/forum/?zx=489otlptm5fw#!mydiscussions/docs/OsltWNvu2Dg)
I am trying to pull data, but with user selected input. If a user decides they want to view data from a certain column on another sheet then they can put an "x" in a cell. I have a helper column that helps write the formula prior to pulling the particular information.
="select C, D, E, F, G, H where B1:B="x"", which gives me a parse error.
Please view: https://docs.google.com/
----------------------------------------------------------------------------------------------
following is a solution based on my understanding of the problem
Mr. Anand,
ReplyDeleteI think K1:K9 is supposed to be B1:B9, so
=query(index(Info!A:I);"select "&join(",","Col"&query(index(B1:B9);"select* where Col1 is not null ")))
----
Then I receive the following error: Invalid query: Column [Colx] cannot appear more than once in SELECT
Hi Bee Lini:
ReplyDeleteSorry about my typo in writing the formula ... the solution I proposed works fine -- the correct formula is:
=query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B1:B9="x",row(B1:B9),));"select* where Col1 is not null ")))
The blog post does show the formula correctly now. Let me know how it works out for you now.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Fantastic!
ReplyDeleteThank you for your solution, and thanks for corresponding back and fourth with me while I described my problem.
The solution works perfectly as was described. Can I add one additional piece of information to the formula? I thought I would be able to add this myself, but the formula became more complex than expected.
ReplyDeleteA2 is now Data Validation, and it pulls all the student names from 'Info'. A user will select a specific name, with
=query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B1:B9="x",row(B1:B9),));"select* where Col1 is not null ")))....I just don't know where to put A2
I updated Sheet1 incase you want to see expected results
Hi Bee Lini:
ReplyDeletePlease update your spreadsheet so I can see where you have the Data Validation for Name ... currently cell A2 in your Sheet1 has Date (field name) in it -- so your Data Validation for Name can not be in Sheet1!A2 and has to be some place else.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Gosh, I'm sorry. I made the typo this time. Sheet1!B1 is the Data Validation cell
ReplyDeleteHi Bee Lini:
ReplyDeleteNo ... cell B1 is being used for presence of x to check whether name field should be used in the QUERY output.
If you need to have Data Validation in Sheet1 keep it outside of cells A1:B9, you can use any cell in column C, say cell C1 for example.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
I removed B1 from being part of the presence of x. It now begins at B2. I have no problem changing it to C1 though.
ReplyDeleteMy follow up question is how do I incorporate the TEXT of a DV cell into the formula =query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B2:B9="x",row(B2:B9),));"select* where Col1 is not null ")))
So basically it filters/querys B1 for a name, and THEN it does the =query(index(Info!A:I);"select "&join(",","Col"&query(index(if(B2:B9="x",row(B2:B9),));"select* where Col1 is not null ")))
Hi Bee Lini:
ReplyDeleteI have added a Data Validation list in cell C4 of yogi_Sheet2 -- see the solution for selecting QUERY output for select columns and selected Name -- see the solution in yogi_Sheet2.
Let me know how it goes.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Yogi_Sheet1, and Sheet1 both reflect what I'm trying to do, I just cant figure out where to add B1 to the wonderful formula you created
ReplyDeletehttps://docs.google.com/spreadsheet/ccc?key=0AvTqJBK5ZdjNdFRCV0Uxd3RZOXdVMTVtWmt2dDJSMHc#gid=0
You my friend are a genius! Many many many thanks
ReplyDeleteYou Are Very Welcome Bee Lini ... Now Let us Keep Googling.
ReplyDeleteMake It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi Yogi,
ReplyDeleteAfter a few weeks of playing with the amazing data you assisted me, I am back for one final question.
I was wondering if its possible to sort a start date, and an end date. The report will only show dates that fall in between start date/end date.
I edited the Yogi_Sheet1 to give an example. Here is the link:
https://docs.google.com/spreadsheet/ccc?key=0AvTqJBK5ZdjNdFRCV0Uxd3RZOXdVMTVtWmt2dDJSMHc#gid=2