Incredibly clever way to get really useful functionality. I’m sorry one person disliked your video. I can only think they must be mentally disturbed. There is nothing to dislike here and you’re an excellent teacher. Thank you for all of your amazingly useful videos!
You are bloody brilliant. I’ve been really stuck on making some dynamic lists from checkboxed items, on my sheet, and now I have so many ideas on how I can fix it. My headache is finally lifting and I feel so relieved. This channel is a godsend; thank you sooo much. DX
I am excited about every one of your videos. Even with topics that I already know, I still learn something new, fantastic. As a non-native English speaker, it's often difficult to follow on complex topics. Not with you, your pronunciation is perfect. When I watch a video of you, there is no language barrier, I understand everything. It's all completely normal, like a native speaker. Crazy :) I love your channel and hope you keep making great content for a long time.
You are such a god send, I would still be a complete noob without your videos, now query and vlookup are like the back of my hand, thank you for all that you do
This is a time saver trick.. i use it in my office to prepare reports.. different columns r required in different reports.. i just have to maintain the main data.. reports can be generated in few clicks.
Do you think it would be possible to also use drop down cell references and to select the column and the where clause. By that I mean for the where clauses you have at the end would it be possible to have one drop down that with a list of column headers and a dependent drop down next to it that displays the unique values from that column?
I was doing really well up to the query. Instead of having the checkboxes in a row, I have those in one column. But when I do the Query I still get the value error, even if using the curly brackets. Select example here: ="SELECT "&TEXTJOIN(", ", TRUE, ArrayFormula(IF(Data!J5:J1371=TRUE,"Row"&(ROW(Data!C3:C1371)-ROW(Data!C4)+2),"")))
Great tutorial sir! Is there a way that I could select, unselect and select all data in a certain range dynamically thru data validation? Hope you may read my comment. Thank you very much sir!
Hi sir thanks for your reply. example if I had 10 sales rep and i will filter their individual sales using query by having a drop down list of their names in an assigned cell, by this manner, I could only select 1 sales rep at a time, however, what will I do to have an option to display all the sale's rep sales? Like I can select "ALL Rep" in a dropdown list to display all their records and not just individual? Hope you can help. Thank you sir!@@ExcelGoogleSheets
Sir i have question for you, Why importrange formula is slow, If i using a importrang formula then sheet are going to slow. For example #Ref, Please help me Sir
Is it possible to filter the data displayed as a result of this formula from a drop down? Wanted to do this in order to display a report but haven't had success doing so.
@@ExcelGoogleSheets If all the rows and columns have some data, then the Query function works well. But if there is a blank cell in the data range, then it throws the Value error. Is there a way we can do a filter outside or inside the query function?
Hello Learn Google Spreadsheets. I have tried your example and everything worked well until I haven't included the curly bracets as you indicated. Would you mind to help out here? Here is the formula> =QUERY({Trading!B10:AO11};"SELECT "&TEXTJOIN("; ";TRUE;ArrayFormula(IF(Trading!B9:AO9;"Col"&(Column(Trading!B10:AO10)-Column(Trading!B10)+1);"")));1) After including those curly bracets a #Value error still comes up and says: Failed to parse query string for Parameter 2 of QUERY function: PARSE_ERROR: Encountered " "; "" at line 1, column 12. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ...
@@ExcelGoogleSheets I followed the video but when I transfer the formula from my source data to my query function in another file it doesn't seems to work. It turn out to be #error.
=arrayformula(QUERY(to_text(importrange("WEBSITE ADDRESS","2021!A10:W266")),"SELECT "&textjoin(", ",true,ArrayFormula(if(A9:X9, "Col"&( column(A10:X10)),""))) WHERE Col4='DENNY' AND Col13 MATCHES '.*[a-zA-Z#*/-]' ORDER BY Col4 asc",1))")) this is my formula and I noticed that my A9:X9, A10,X10 is not referenced from the other file which it is supposed to be referenced from other file. I change the document address to WEBSITE ADDRESS :)
I am trying to do it by rows, not columns but it not work with me, can you help me with it? =QUERY({LIVE!B2:F1000},"SELECT "&TEXTJOIN(", ",true,ArrayFormula(IF(LIVE!A2:A1000,"ROW"&ROW(LIVE!B2:B1000)-ROW(LIVE!B2)+1,""))),1) This the formula which I did
Hi Omar, I have tried a similar case. Be aware SELECT is for columns & WHERE is for rows only: select Selects which columns to return, and in what order. If omitted, all of the table's columns are returned, in their default order. where Returns only rows that match a condition. If omitted, all rows are returned. Source: developers.google.com/chart/interactive/docs/querylanguage
Try this for your case (assuming the checkboxes are in column A = Col1) =QUERY({LIVE!A1:F}, "SELECT *" &"WHERE Col1 = TRUE") For me, this works fine, much easier as well!
Teacher...don't know if my Layout will come through but this is a Cut & Paste of what I have: MileageEach WayRound Trip37.3 =ARRAYFORMULA(IF((B2:B13)="","",(B4:B15)-OFFSET(B4:B15,-2,0))) without #N/A 178777.7178777.737.3 =ARRAYFORMULA(IF((B2:B15)="","",(B4:B15)-OFFSET(B4:B15,-2,0))) with #N/A 9668.6Card # 4178815.0178815.037.337.349706.00.07.537.3 =ARRAYFORMULA(B4:B15-OFFSET(B4:B15,-2,0)) 178819.0178819.04.041.37.50.09710.01.34.0178826.5178826.57.548.81.30.09717.4-178827.87.537.3 =IF(OFFSET(C4,-2,-1)="","",OFFSET(C4,0,-1)-OFFSET(C4,-2,-1)) 178827.8178827.81.350.1-178827.80.09718.81.337.3 =IF(OFFSET(D2,0,-2)="","",OFFSET(B4,0,0)-OFFSET(B2,0,0)) 178833.6-178827.8-178777.70.09724.5-178827.8178834.0178834.056.3#N/A0.09724.99724.9#N/A178834.09724.91 =ARRAYFORMULA((--(B2:B15"")*(--(B4:B17)""))) 0 AUTOFILL Arrays Auto Drag Down Formulas with Arrays Learn Google Sheets@UA-cam.Com 101 =ARRAYFORMULA((--(B2:B13"")*(--(B4:B15)""))) 100110011001000110114 Rows0012 Rows =ARRAYFORMULA((--(B2:B15"")*(--(B4:B17)""))) =IF(OFFSET(C4,-2,-1)="","",OFFSET(C4,0,-1)-OFFSET(C4,-2,-1)) =ARRAYFORMULA((--(B2:B15"")*(--(B4:B17)""))) =IF(OFFSET(C4,-2,-1)="","",OFFSET(C4,0,-1)-OFFSET(C4,-2,-1)) =ARRAYFORMULA( IF(--(B2:B15"")*(--(B4:B17)"")) ) OFFSET(C4,0,-1)-OFFSET(C4,-2,-1 37.3 =IF((--(B2""))*(--(B4"")), OFFSET(C4,0,-1)-OFFSET(C4,-2,-1 ),"") 4.037.3 =ARRAYFORMULA( IF((--(B2:B15""))*(--(B4:B17"")), OFFSET(C4,0,-1)-OFFSET(C4,-2,-1 ),""))
@@ExcelGoogleSheets THANK YOU SOO SOO MUCH! =QUERY({D6:KK101},"SELECT "&TEXTJOIN(", ",TRUE,ArrayFormula(IF('TRANSPOSE 2'!D3:3, "COL"&(COLUMN('TRANSPOSE 2'!D18:18)-COLUMN('TRANSPOSE 2'!C2),"")))))
I'm not sure if there are other problems, but one problem I can see right away is that COL should be Col. It's case sensitive and you should also have a space after it, like "Col "
![Lp. Последняя Реальность #107 РОДНОЙ ДОМ [Финал] • Майнкрафт](http://i.ytimg.com/vi/IK3QKzKUlHM/mqdefault.jpg)
Incredibly clever way to get really useful functionality. I’m sorry one person disliked your video. I can only think they must be mentally disturbed. There is nothing to dislike here and you’re an excellent teacher. Thank you for all of your amazingly useful videos!
It's fun because at work they think I'm a spreadsheet wizard thanks to you.
Same here bro
I don't know how you do to have such amazing ideas and skills to teach what you're doing...Your videos really helped and still help me. Very good job
Wow, thank you!
I just finished doing almost exactly this in my way YESTERDAY.... Guess I'll start over again, as your way is much more appealing and flexible! 😊
You are bloody brilliant. I’ve been really stuck on making some dynamic lists from checkboxed items, on my sheet, and now I have so many ideas on how I can fix it. My headache is finally lifting and I feel so relieved. This channel is a godsend; thank you sooo much. DX
:)
Dude! That is so brilliant!! You've been blowing my mind for years.
Very cool idea. I really like the way you put the text for the query in a cell so it is clearly visible and easy to follow.
I am excited about every one of your videos. Even with topics that I already know, I still learn something new, fantastic.
As a non-native English speaker, it's often difficult to follow on complex topics. Not with you, your pronunciation is perfect. When I watch a video of you, there is no language barrier, I understand everything. It's all completely normal, like a native speaker. Crazy :)
I love your channel and hope you keep making great content for a long time.
Great to hear!
You are such a god send, I would still be a complete noob without your videos, now query and vlookup are like the back of my hand, thank you for all that you do
Awesome!
Thanks for teaching us this, the way you analyze problems is incredible, thank you very much!
Glad you like them!
Excellent video! Thanks a lot for sharing😀
Really that much valuable content sir! ❤ prayers and good wishes for your channel from India 🙏🏿
I just Love Dynamic !
Muito bom o vídeo.
Acrescentou muito ao meu projeto.
Thank you thank you thank you!
Thanks. It is an excellent expression and a work full of intelligence.
You changed the way I think and write formulas.
Glad to hear that!
I really like your sharing. I apply your guidelines a lot at work. Thank you a lot!
Happy to hear that!
Thanks! Very helpful
This is a time saver trick.. i use it in my office to prepare reports.. different columns r required in different reports.. i just have to maintain the main data.. reports can be generated in few clicks.
Cool way to work around 😘
Excelent!!!!
Brilliant 👍🏻👍🏻👍🏻
Thank you!
Glad you liked it!
Do you think it would be possible to also use drop down cell references and to select the column and the where clause.
By that I mean for the where clauses you have at the end would it be possible to have one drop down that with a list of column headers and a dependent drop down next to it that displays the unique values from that column?
Superb Sir
Can this also be done vertically for rows?
Dope! I use a variation of this
Cool!
Thank you
The True Master... Always on sharp edge. of knowledge....High skill
So How I can get the file for practice.
Very cool
Excellent
Great Nice Best Unique
Thank you! Cheers!
I was doing really well up to the query. Instead of having the checkboxes in a row, I have those in one column. But when I do the Query I still get the value error, even if using the curly brackets.
Select example here:
="SELECT "&TEXTJOIN(", ", TRUE, ArrayFormula(IF(Data!J5:J1371=TRUE,"Row"&(ROW(Data!C3:C1371)-ROW(Data!C4)+2),"")))
Very useful, great video
Glad you liked it
Amazing
Brilliant
Excellent. You keep opening new avenues to things which can be done here. Can you include a boolean test here.
Sure
LoL. Wonderful. 👏👏👏
Amazing video as always, thanks:) Can you make video on how to integrate whatsapp with sheets?
No free publicly available API for whatsapp.
is there any way I can get a sample sheet with the commands?
Cara Ilário de mais
Would be nice if you share the link to your project files to practice on. Thanks
In this checkbox model, we can use this formula also to extract specific columns in the same way. "=FILTER(TEXT!A2:T,TEXT!A1:T1=TRUE)"
Would this work when two or more people are accessing the sheet simultaneously and selecting different checkboxes?
Changes will affect both of them.
Is there a way to do this but using checkboxes to select rows of data? Columns arent friendly to larger data sets.
Great tutorial sir! Is there a way that I could select, unselect and select all data in a certain range dynamically thru data validation? Hope you may read my comment. Thank you very much sir!
What you mean by select thru data validation?
Hi sir thanks for your reply. example if I had 10 sales rep and i will filter their individual sales using query by having a drop down list of their names in an assigned cell, by this manner, I could only select 1 sales rep at a time, however, what will I do to have an option to display all the sale's rep sales? Like I can select "ALL Rep" in a dropdown list to display all their records and not just individual? Hope you can help. Thank you sir!@@ExcelGoogleSheets
ua-cam.com/video/nLW8SerwnJo/v-deo.html
@@ExcelGoogleSheets Thank you very much sir! God bless!
👍
What if we collecting the data not based from column, but from the row. Can I change =COLUMN with =ROW formula? Thanks in advance
Its very simple.. use “where” in query.. e.g. “where Col2=TRUE”.. i have done it
Sir i have question for you,
Why importrange formula is slow, If i using a importrang formula then sheet are going to slow. For example #Ref,
Please help me Sir
Is it possible to filter the data displayed as a result of this formula from a drop down? Wanted to do this in order to display a report but haven't had success doing so.
yes, it''s possible.
@@ExcelGoogleSheets can you elaborate on this? I have been trying to get it to work with no success.
ua-cam.com/video/nLW8SerwnJo/v-deo.html
SUPPOSE I HAVE TWO DIIFERENT TABLE I USE THAT TO COMBINE THOSE DATA
How do I preserve hyperlinks with Query Function? Thanks
Use FILTER function instead.
If there is a blank data in a column, it throws a #VALUE error. Is there any alternate method to either Filter or ignore that?
What do you mean by "blank data"?
@@ExcelGoogleSheets If all the rows and columns have some data, then the Query function works well. But if there is a blank cell in the data range, then it throws the Value error. Is there a way we can do a filter outside or inside the query function?
There must be something else happening. Just having a blank cell shouldn't cause this issue. Can you create a sample sheet and share it here?
Isn't there a simpler way to activate searches through. a checkbox
Hello Learn Google Spreadsheets. I have tried your example and everything worked well until I haven't included the curly bracets as you indicated. Would you mind to help out here?
Here is the formula> =QUERY({Trading!B10:AO11};"SELECT "&TEXTJOIN("; ";TRUE;ArrayFormula(IF(Trading!B9:AO9;"Col"&(Column(Trading!B10:AO10)-Column(Trading!B10)+1);"")));1)
After including those curly bracets a #Value error still comes up and says:
Failed to parse query string for Parameter 2 of QUERY function: PARSE_ERROR: Encountered " "; "" at line 1, column 12. Was expecting one of: "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ...
TEXTJOIN("; " should be TEXTJOIN(", "
@@ExcelGoogleSheets Thank you mate, it worked wonders
Hi i'd like to ask whether this method can be applicable if i am making query from another file?
Yes, it is.
@@ExcelGoogleSheets I followed the video but when I transfer the formula from my source data to my query function in another file it doesn't seems to work. It turn out to be #error.
@@malcovishes You'll need to use it with IMPORTRANGE function.
=arrayformula(QUERY(to_text(importrange("WEBSITE ADDRESS","2021!A10:W266")),"SELECT "&textjoin(", ",true,ArrayFormula(if(A9:X9, "Col"&( column(A10:X10)),""))) WHERE Col4='DENNY' AND Col13 MATCHES '.*[a-zA-Z#*/-]' ORDER BY Col4 asc",1))"))
this is my formula and I noticed that my A9:X9, A10,X10 is not referenced from the other file which it is supposed to be referenced from other file. I change the document address to WEBSITE ADDRESS :)
@@malcovishes Each time you reference to a range you need importrange function, so in this you'll need 3 importrange functions.
I am trying to do it by rows, not columns but it not work with me, can you help me with it?
=QUERY({LIVE!B2:F1000},"SELECT "&TEXTJOIN(", ",true,ArrayFormula(IF(LIVE!A2:A1000,"ROW"&ROW(LIVE!B2:B1000)-ROW(LIVE!B2)+1,""))),1) This the formula which I did
=FILTER(LIVE!B2:F1000,LIVE!A2:A1000)
Hi Omar,
I have tried a similar case. Be aware SELECT is for columns & WHERE is for rows only:
select Selects which columns to return, and in what order. If omitted, all of the table's columns are returned, in their default order.
where Returns only rows that match a condition. If omitted, all rows are returned.
Source: developers.google.com/chart/interactive/docs/querylanguage
Try this for your case (assuming the checkboxes are in column A = Col1)
=QUERY({LIVE!A1:F},
"SELECT *"
&"WHERE Col1 = TRUE")
For me, this works fine, much easier as well!
Teacher...how do you handle: array arguments to multiply are of different size
Looks like syntax error. What's the formula?
@@ExcelGoogleSheets =ARRAYFORMULA((--(OFFSET(C2,0,-1):OFFSET(C2,13,-1)"")*(--(OFFSET(C2,2,-1):OFFSET(C2,15,-1)""))))
Teacher...don't know if my Layout will come through but this is a Cut & Paste of what I have:
MileageEach WayRound Trip37.3
=ARRAYFORMULA(IF((B2:B13)="","",(B4:B15)-OFFSET(B4:B15,-2,0))) without #N/A
178777.7178777.737.3
=ARRAYFORMULA(IF((B2:B15)="","",(B4:B15)-OFFSET(B4:B15,-2,0))) with #N/A
9668.6Card # 4178815.0178815.037.337.349706.00.07.537.3
=ARRAYFORMULA(B4:B15-OFFSET(B4:B15,-2,0))
178819.0178819.04.041.37.50.09710.01.34.0178826.5178826.57.548.81.30.09717.4-178827.87.537.3
=IF(OFFSET(C4,-2,-1)="","",OFFSET(C4,0,-1)-OFFSET(C4,-2,-1))
178827.8178827.81.350.1-178827.80.09718.81.337.3
=IF(OFFSET(D2,0,-2)="","",OFFSET(B4,0,0)-OFFSET(B2,0,0))
178833.6-178827.8-178777.70.09724.5-178827.8178834.0178834.056.3#N/A0.09724.99724.9#N/A178834.09724.91
=ARRAYFORMULA((--(B2:B15"")*(--(B4:B17)"")))
0
AUTOFILL Arrays Auto Drag Down Formulas with Arrays Learn Google Sheets@UA-cam.Com
101
=ARRAYFORMULA((--(B2:B13"")*(--(B4:B15)"")))
100110011001000110114 Rows0012 Rows
=ARRAYFORMULA((--(B2:B15"")*(--(B4:B17)"")))
=IF(OFFSET(C4,-2,-1)="","",OFFSET(C4,0,-1)-OFFSET(C4,-2,-1))
=ARRAYFORMULA((--(B2:B15"")*(--(B4:B17)"")))
=IF(OFFSET(C4,-2,-1)="","",OFFSET(C4,0,-1)-OFFSET(C4,-2,-1))
=ARRAYFORMULA( IF(--(B2:B15"")*(--(B4:B17)"")) )
OFFSET(C4,0,-1)-OFFSET(C4,-2,-1
37.3
=IF((--(B2""))*(--(B4"")), OFFSET(C4,0,-1)-OFFSET(C4,-2,-1 ),"")
4.037.3
=ARRAYFORMULA( IF((--(B2:B15""))*(--(B4:B17"")), OFFSET(C4,0,-1)-OFFSET(C4,-2,-1 ),""))
I don't get any errors with this formula, even though I don't really understand what you're trying to accomplish. Share an example sheet.
@@ExcelGoogleSheets Teacher...what is the best way to get an example over to you?
TEXTJOIN function not ignoring empty even after using TRUE
That means they are not really empty even though it may look like they are empty.
THE CURLY BRACKETS ARE NOT WORKING FOR ME AT THE END
#VALUE!
Please share your formula. I can't help you by reading your comment.
@@ExcelGoogleSheets THANK YOU SOO SOO MUCH!
=QUERY({D6:KK101},"SELECT "&TEXTJOIN(", ",TRUE,ArrayFormula(IF('TRANSPOSE 2'!D3:3, "COL"&(COLUMN('TRANSPOSE 2'!D18:18)-COLUMN('TRANSPOSE 2'!C2),"")))))
I'm not sure if there are other problems, but one problem I can see right away is that COL should be Col. It's case sensitive and you should also have a space after it, like "Col "
this is better than porn!
Thank you