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!
@_sherps28314 ай бұрын
Your videos have been extremely helpful with me improving the functionality of my spreadsheets for work. I am actually working on combining the ideas from your other video "QUERY - Drop Down List to Filter Data - Google Sheets" with this video as well. Thanks a lot for your great work.
@stephanembatchou70373 жыл бұрын
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
@ExcelGoogleSheets3 жыл бұрын
Wow, thank you!
@rion24993 жыл бұрын
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
@ExcelGoogleSheets3 жыл бұрын
:)
@MarcCastellsBallesta3 жыл бұрын
It's fun because at work they think I'm a spreadsheet wizard thanks to you.
@hemantsonawane1532 жыл бұрын
Same here bro
@QuadDrums3 жыл бұрын
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
@ExcelGoogleSheets3 жыл бұрын
Awesome!
@marcpennings78813 жыл бұрын
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! 😊
@customselectinc55133 жыл бұрын
Can this also be done vertically for rows?
@RoseLK3 жыл бұрын
I really like your sharing. I apply your guidelines a lot at work. Thank you a lot!
@ExcelGoogleSheets3 жыл бұрын
Happy to hear that!
@davidbeauchamp21612 жыл бұрын
Dude! That is so brilliant!! You've been blowing my mind for years.
@lafamillecarrington2 жыл бұрын
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.
@mz60163 жыл бұрын
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.
@ExcelGoogleSheets3 жыл бұрын
Great to hear!
@yingliu-kneip14443 жыл бұрын
Excellent video! Thanks a lot for sharing😀
@byaheniangkol2 ай бұрын
The best tutoriak
@NB_Miraj9 ай бұрын
Really that much valuable content sir! ❤ prayers and good wishes for your channel from India 🙏🏿
@millerjs17593 жыл бұрын
Thanks for teaching us this, the way you analyze problems is incredible, thank you very much!
@ExcelGoogleSheets3 жыл бұрын
Glad you like them!
@hemantsonawane1532 жыл бұрын
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.
@mehmetgazel33113 жыл бұрын
Thanks. It is an excellent expression and a work full of intelligence. You changed the way I think and write formulas.
@ExcelGoogleSheets3 жыл бұрын
Glad to hear that!
@tanveerhussain38813 жыл бұрын
Great Nice Best Unique
@ExcelGoogleSheets3 жыл бұрын
Thank you! Cheers!
@elricho723 жыл бұрын
Very useful, great video
@ExcelGoogleSheets3 жыл бұрын
Glad you liked it
@kulvinder82113 жыл бұрын
Superb Sir
@dajnehik2 жыл бұрын
Would this work when two or more people are accessing the sheet simultaneously and selecting different checkboxes?
@ExcelGoogleSheets2 жыл бұрын
Changes will affect both of them.
@nisargagza3 жыл бұрын
Cool way to work around 😘
@dickgrayson56693 жыл бұрын
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?
@mercyfrs3 жыл бұрын
The True Master... Always on sharp edge. of knowledge....High skill So How I can get the file for practice.
@Puner543 жыл бұрын
I just Love Dynamic !
@abrahamhahijary2 жыл бұрын
Thanks! Very helpful
@ManojGuptahome3 жыл бұрын
Excellent. You keep opening new avenues to things which can be done here. Can you include a boolean test here.
@ExcelGoogleSheets3 жыл бұрын
Sure
@magickemmy458611 ай бұрын
Is there a way to do this but using checkboxes to select rows of data? Columns arent friendly to larger data sets.
@tridsonline3 жыл бұрын
Brilliant 👍🏻👍🏻👍🏻 Thank you!
@ExcelGoogleSheets3 жыл бұрын
Glad you liked it!
@cryptovaulteurope8563 жыл бұрын
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" ... "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... "/" ... "%" ... "+" ... "-" ...
@ExcelGoogleSheets3 жыл бұрын
TEXTJOIN("; " should be TEXTJOIN(", "
@cryptovaulteurope8563 жыл бұрын
@@ExcelGoogleSheets Thank you mate, it worked wonders
@traviswalker89333 жыл бұрын
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.
@ExcelGoogleSheets3 жыл бұрын
yes, it''s possible.
@traviswalker89333 жыл бұрын
@@ExcelGoogleSheets can you elaborate on this? I have been trying to get it to work with no success.
@ExcelGoogleSheets3 жыл бұрын
kzbin.info/www/bejne/pH26aYabp9yhgNE
@menachemkantor63513 жыл бұрын
is there any way I can get a sample sheet with the commands?
@ДарьяКроткова-ч5о3 жыл бұрын
Thank you thank you thank you!
@omarchtchareto37013 жыл бұрын
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
@ExcelGoogleSheets3 жыл бұрын
=FILTER(LIVE!B2:F1000,LIVE!A2:A1000)
@ernst-joergoberhoessel83633 жыл бұрын
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
@ernst-joergoberhoessel83633 жыл бұрын
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!
@omprakash.youtube3 жыл бұрын
Excellent
@channeldhita3 жыл бұрын
What if we collecting the data not based from column, but from the row. Can I change =COLUMN with =ROW formula? Thanks in advance
@hemantsonawane1532 жыл бұрын
Its very simple.. use “where” in query.. e.g. “where Col2=TRUE”.. i have done it
@juanitae25442 жыл бұрын
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),"")))
@professoramaral12813 жыл бұрын
Muito bom o vídeo. Acrescentou muito ao meu projeto.
@kynnor3 жыл бұрын
Very cool
@ghufranmalik15293 жыл бұрын
Would be nice if you share the link to your project files to practice on. Thanks
@JustVid3 жыл бұрын
Amazing video as always, thanks:) Can you make video on how to integrate whatsapp with sheets?
@ExcelGoogleSheets3 жыл бұрын
No free publicly available API for whatsapp.
@mauroassoni3 жыл бұрын
How do I preserve hyperlinks with Query Function? Thanks
@ExcelGoogleSheets3 жыл бұрын
Use FILTER function instead.
@nhule84423 жыл бұрын
Amazing
@Puner543 жыл бұрын
Teacher...how do you handle: array arguments to multiply are of different size
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@KZbin.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 ),""))
@ExcelGoogleSheets3 жыл бұрын
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.
@Puner543 жыл бұрын
@@ExcelGoogleSheets Teacher...what is the best way to get an example over to you?
@malcovishes3 жыл бұрын
Hi i'd like to ask whether this method can be applicable if i am making query from another file?
@ExcelGoogleSheets3 жыл бұрын
Yes, it is.
@malcovishes3 жыл бұрын
@@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.
@ExcelGoogleSheets3 жыл бұрын
@@malcovishes You'll need to use it with IMPORTRANGE function.
@malcovishes3 жыл бұрын
=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 :)
@ExcelGoogleSheets3 жыл бұрын
@@malcovishes Each time you reference to a range you need importrange function, so in this you'll need 3 importrange functions.
@giancarloschafloqueguzman99183 жыл бұрын
Excelent!!!!
@mdpdme7383 жыл бұрын
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
@edwinrondina54912 жыл бұрын
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!
@ExcelGoogleSheets2 жыл бұрын
What you mean by select thru data validation?
@edwinrondina54912 жыл бұрын
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
@ExcelGoogleSheets2 жыл бұрын
kzbin.info/www/bejne/pH26aYabp9yhgNE
@edwinrondina54912 жыл бұрын
@@ExcelGoogleSheets Thank you very much sir! God bless!
@ExcelGoogleSheets2 жыл бұрын
👍
@close2allofu3 жыл бұрын
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?
@ExcelGoogleSheets3 жыл бұрын
What do you mean by "blank data"?
@close2allofu3 жыл бұрын
@@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?
@ExcelGoogleSheets3 жыл бұрын
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?
@mochannel2482 Жыл бұрын
Brilliant
@benponderin3 жыл бұрын
Dope! I use a variation of this
@ExcelGoogleSheets3 жыл бұрын
Cool!
@Adaptale_Minds2 жыл бұрын
SUPPOSE I HAVE TWO DIIFERENT TABLE I USE THAT TO COMBINE THOSE DATA
@suryanarayanmohapatra7190 Жыл бұрын
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)"
@lucianopessanha74373 жыл бұрын
LoL. Wonderful. 👏👏👏
@khaledaboelhamd3 жыл бұрын
Thank you
@ScottIrvin-CDPHE Жыл бұрын
THE CURLY BRACKETS ARE NOT WORKING FOR ME AT THE END
@ScottIrvin-CDPHE Жыл бұрын
#VALUE!
@ExcelGoogleSheets Жыл бұрын
Please share your formula. I can't help you by reading your comment.
@ScottIrvin-CDPHE Жыл бұрын
@@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),"")))))
@ExcelGoogleSheets Жыл бұрын
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 "
@adeelahmad-qk7rv2 жыл бұрын
TEXTJOIN function not ignoring empty even after using TRUE
@ExcelGoogleSheets2 жыл бұрын
That means they are not really empty even though it may look like they are empty.