Tap to unmute

QUERY Function - Select Columns with Checkboxes - Google Sheets

  Рет қаралды 39,091

Learn Google Sheets & Excel Spreadsheets

Learn Google Sheets & Excel Spreadsheets

Күн бұрын

Пікірлер
@garychadwick1240
@garychadwick1240 3 жыл бұрын
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!
@_sherps2831
@_sherps2831 4 ай бұрын
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.
@stephanembatchou7037
@stephanembatchou7037 3 жыл бұрын
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
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Wow, thank you!
@rion2499
@rion2499 3 жыл бұрын
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
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
:)
@MarcCastellsBallesta
@MarcCastellsBallesta 3 жыл бұрын
It's fun because at work they think I'm a spreadsheet wizard thanks to you.
@hemantsonawane153
@hemantsonawane153 2 жыл бұрын
Same here bro
@QuadDrums
@QuadDrums 3 жыл бұрын
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
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Awesome!
@marcpennings7881
@marcpennings7881 3 жыл бұрын
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! 😊
@customselectinc5513
@customselectinc5513 3 жыл бұрын
Can this also be done vertically for rows?
@RoseLK
@RoseLK 3 жыл бұрын
I really like your sharing. I apply your guidelines a lot at work. Thank you a lot!
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Happy to hear that!
@davidbeauchamp2161
@davidbeauchamp2161 2 жыл бұрын
Dude! That is so brilliant!! You've been blowing my mind for years.
@lafamillecarrington
@lafamillecarrington 2 жыл бұрын
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.
@mz6016
@mz6016 3 жыл бұрын
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.
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Great to hear!
@yingliu-kneip1444
@yingliu-kneip1444 3 жыл бұрын
Excellent video! Thanks a lot for sharing😀
@byaheniangkol
@byaheniangkol 2 ай бұрын
The best tutoriak
@NB_Miraj
@NB_Miraj 9 ай бұрын
Really that much valuable content sir! ❤ prayers and good wishes for your channel from India 🙏🏿
@millerjs1759
@millerjs1759 3 жыл бұрын
Thanks for teaching us this, the way you analyze problems is incredible, thank you very much!
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Glad you like them!
@hemantsonawane153
@hemantsonawane153 2 жыл бұрын
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.
@mehmetgazel3311
@mehmetgazel3311 3 жыл бұрын
Thanks. It is an excellent expression and a work full of intelligence. You changed the way I think and write formulas.
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Glad to hear that!
@tanveerhussain3881
@tanveerhussain3881 3 жыл бұрын
Great Nice Best Unique
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Thank you! Cheers!
@elricho72
@elricho72 3 жыл бұрын
Very useful, great video
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Glad you liked it
@kulvinder8211
@kulvinder8211 3 жыл бұрын
Superb Sir
@dajnehik
@dajnehik 2 жыл бұрын
Would this work when two or more people are accessing the sheet simultaneously and selecting different checkboxes?
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
Changes will affect both of them.
@nisargagza
@nisargagza 3 жыл бұрын
Cool way to work around 😘
@dickgrayson5669
@dickgrayson5669 3 жыл бұрын
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?
@mercyfrs
@mercyfrs 3 жыл бұрын
The True Master... Always on sharp edge. of knowledge....High skill So How I can get the file for practice.
@Puner54
@Puner54 3 жыл бұрын
I just Love Dynamic !
@abrahamhahijary
@abrahamhahijary 2 жыл бұрын
Thanks! Very helpful
@ManojGuptahome
@ManojGuptahome 3 жыл бұрын
Excellent. You keep opening new avenues to things which can be done here. Can you include a boolean test here.
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Sure
@magickemmy4586
@magickemmy4586 11 ай бұрын
Is there a way to do this but using checkboxes to select rows of data? Columns arent friendly to larger data sets.
@tridsonline
@tridsonline 3 жыл бұрын
Brilliant 👍🏻👍🏻👍🏻 Thank you!
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Glad you liked it!
@cryptovaulteurope856
@cryptovaulteurope856 3 жыл бұрын
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
@ExcelGoogleSheets 3 жыл бұрын
TEXTJOIN("; " should be TEXTJOIN(", "
@cryptovaulteurope856
@cryptovaulteurope856 3 жыл бұрын
@@ExcelGoogleSheets Thank you mate, it worked wonders
@traviswalker8933
@traviswalker8933 3 жыл бұрын
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
@ExcelGoogleSheets 3 жыл бұрын
yes, it''s possible.
@traviswalker8933
@traviswalker8933 3 жыл бұрын
@@ExcelGoogleSheets can you elaborate on this? I have been trying to get it to work with no success.
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
kzbin.info/www/bejne/pH26aYabp9yhgNE
@menachemkantor6351
@menachemkantor6351 3 жыл бұрын
is there any way I can get a sample sheet with the commands?
@ДарьяКроткова-ч5о
@ДарьяКроткова-ч5о 3 жыл бұрын
Thank you thank you thank you!
@omarchtchareto3701
@omarchtchareto3701 3 жыл бұрын
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
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
=FILTER(LIVE!B2:F1000,LIVE!A2:A1000)
@ernst-joergoberhoessel8363
@ernst-joergoberhoessel8363 3 жыл бұрын
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-joergoberhoessel8363
@ernst-joergoberhoessel8363 3 жыл бұрын
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.youtube
@omprakash.youtube 3 жыл бұрын
Excellent
@channeldhita
@channeldhita 3 жыл бұрын
What if we collecting the data not based from column, but from the row. Can I change =COLUMN with =ROW formula? Thanks in advance
@hemantsonawane153
@hemantsonawane153 2 жыл бұрын
Its very simple.. use “where” in query.. e.g. “where Col2=TRUE”.. i have done it
@juanitae2544
@juanitae2544 2 жыл бұрын
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),"")))
@professoramaral1281
@professoramaral1281 3 жыл бұрын
Muito bom o vídeo. Acrescentou muito ao meu projeto.
@kynnor
@kynnor 3 жыл бұрын
Very cool
@ghufranmalik1529
@ghufranmalik1529 3 жыл бұрын
Would be nice if you share the link to your project files to practice on. Thanks
@JustVid
@JustVid 3 жыл бұрын
Amazing video as always, thanks:) Can you make video on how to integrate whatsapp with sheets?
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
No free publicly available API for whatsapp.
@mauroassoni
@mauroassoni 3 жыл бұрын
How do I preserve hyperlinks with Query Function? Thanks
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Use FILTER function instead.
@nhule8442
@nhule8442 3 жыл бұрын
Amazing
@Puner54
@Puner54 3 жыл бұрын
Teacher...how do you handle: array arguments to multiply are of different size
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Looks like syntax error. What's the formula?
@Puner54
@Puner54 3 жыл бұрын
@@ExcelGoogleSheets =ARRAYFORMULA((--(OFFSET(C2,0,-1):OFFSET(C2,13,-1)"")*(--(OFFSET(C2,2,-1):OFFSET(C2,15,-1)""))))
@Puner54
@Puner54 3 жыл бұрын
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 ),""))
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
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.
@Puner54
@Puner54 3 жыл бұрын
@@ExcelGoogleSheets Teacher...what is the best way to get an example over to you?
@malcovishes
@malcovishes 3 жыл бұрын
Hi i'd like to ask whether this method can be applicable if i am making query from another file?
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Yes, it is.
@malcovishes
@malcovishes 3 жыл бұрын
@@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.
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
@@malcovishes You'll need to use it with IMPORTRANGE function.
@malcovishes
@malcovishes 3 жыл бұрын
=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 :)
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
@@malcovishes Each time you reference to a range you need importrange function, so in this you'll need 3 importrange functions.
@giancarloschafloqueguzman9918
@giancarloschafloqueguzman9918 3 жыл бұрын
Excelent!!!!
@mdpdme738
@mdpdme738 3 жыл бұрын
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
@edwinrondina5491
@edwinrondina5491 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!
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
What you mean by select thru data validation?
@edwinrondina5491
@edwinrondina5491 2 жыл бұрын
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
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
kzbin.info/www/bejne/pH26aYabp9yhgNE
@edwinrondina5491
@edwinrondina5491 2 жыл бұрын
@@ExcelGoogleSheets Thank you very much sir! God bless!
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
👍
@close2allofu
@close2allofu 3 жыл бұрын
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?
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
What do you mean by "blank data"?
@close2allofu
@close2allofu 3 жыл бұрын
@@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?
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
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
@mochannel2482 Жыл бұрын
Brilliant
@benponderin
@benponderin 3 жыл бұрын
Dope! I use a variation of this
@ExcelGoogleSheets
@ExcelGoogleSheets 3 жыл бұрын
Cool!
@Adaptale_Minds
@Adaptale_Minds 2 жыл бұрын
SUPPOSE I HAVE TWO DIIFERENT TABLE I USE THAT TO COMBINE THOSE DATA
@suryanarayanmohapatra7190
@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)"
@lucianopessanha7437
@lucianopessanha7437 3 жыл бұрын
LoL. Wonderful. 👏👏👏
@khaledaboelhamd
@khaledaboelhamd 3 жыл бұрын
Thank you
@ScottIrvin-CDPHE
@ScottIrvin-CDPHE Жыл бұрын
THE CURLY BRACKETS ARE NOT WORKING FOR ME AT THE END
@ScottIrvin-CDPHE
@ScottIrvin-CDPHE Жыл бұрын
#VALUE!
@ExcelGoogleSheets
@ExcelGoogleSheets Жыл бұрын
Please share your formula. I can't help you by reading your comment.
@ScottIrvin-CDPHE
@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
@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-qk7rv
@adeelahmad-qk7rv 2 жыл бұрын
TEXTJOIN function not ignoring empty even after using TRUE
@ExcelGoogleSheets
@ExcelGoogleSheets 2 жыл бұрын
That means they are not really empty even though it may look like they are empty.
@amelhordirecao3548
@amelhordirecao3548 3 жыл бұрын
Cara Ilário de mais
@MahyLune
@MahyLune 3 жыл бұрын
this is better than porn!
@moemagoretros
@moemagoretros 3 жыл бұрын
Thank you
QUERY Function - Variables - Google Sheets
23:06
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 61 М.
QUERY - Drop Down List to Filter Data - Google Sheets
15:25
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 358 М.
黑天使只对C罗有感觉#short #angel #clown
00:39
Super Beauty team
Рет қаралды 36 МЛН
Мен атып көрмегенмін ! | Qalam | 5 серия
25:41
REAL or FAKE? #beatbox #tiktok
01:03
BeatboxJCOP
Рет қаралды 18 МЛН
Microsoft 365 Admin Secrets
21:35
Andy Malone MVP
Рет қаралды 7 М.
QUERY Function in Google Sheets - 2024 Tutorial ✏️
14:33
Coupler․io Academy
Рет қаралды 34 М.
QUERY - Select a Range of Multiple Columns in Google Sheets
16:44
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 80 М.
QUERY & MYSELECT functions, Select by Column Names (Headers, Labels) - Google Sheets
33:56
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 37 М.
Google Sheets - Join Tables using VLOOKUP & QUERY Functions
16:53
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 48 М.
10 HIDDEN SECRETS of Checkboxes in Google Sheets!
17:33
Hash Ali
Рет қаралды 25 М.
Google Sheets Query Function Explained
12:35
Leila Gharani
Рет қаралды 321 М.
Next level FILTER Function tricks | Excel Off The Grid
13:23
Excel Off The Grid
Рет қаралды 35 М.
10 INCREDIBLE things Google Sheets can do Right Now!
13:27
Jeff Su
Рет қаралды 62 М.
Google Sheets - Search,  QUERY function
22:49
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 226 М.
黑天使只对C罗有感觉#short #angel #clown
00:39
Super Beauty team
Рет қаралды 36 МЛН