Learn how to use variables in Google Sheets QUERY function. QUERY Function Series playlist • Google Sheets Query fu... Practice sheet (File - Make a Copy) docs.google.co... #query #google #sheets
Пікірлер: 68
@AliJardz3 жыл бұрын
I'd never thought of using substitute in this - super smart.
@atropolifilm45002 жыл бұрын
THANK GOOODD you saved my nerves. I struggled for three hours to find a solution how to add a variable into a Query function. Fabulous video! You are my go-to KZbin channel for any questions concerning Google Sheets.
@CatZillaVid3 жыл бұрын
You explained what everyone else was skipping. I was searching for dynamic where clause, those shitty top videos with millions of views didn't show it. Thank you 🙏
@SRDFTM3 жыл бұрын
Oh my god! This is the answer, a've been looking for I'm using FILTER and several IMPORTRANGE functions, i think this will help me soooo much Thank you!!
@thebestofbests77463 жыл бұрын
Keep going ❤️
@urntindanger3 жыл бұрын
Great channel guys! I learned a lot from you - especially js and google colab. Also finding googlesheets videos useful as some ways of doing things are completly new for me. 👌🏼👌🏼👌🏼👌🏼 every day is a school day! 😏
@sethhill75733 жыл бұрын
This was super helpful! Saved me from having to hire someone. Thank you.
@nisargagza3 жыл бұрын
Very useful in real world to make dashboard
@davidfernandezbajo3 жыл бұрын
Thank You!
@JBblondin3 жыл бұрын
Amazing work, thanks!
@thethieu20393 жыл бұрын
You're superman!!!
@chrilleswybrandt72623 жыл бұрын
Can I also use regex in the query function to select both west and south? Eg. west|south as the value in the region field. Ps. Your videos are of great value to me. THANKS!
@ExcelGoogleSheets3 жыл бұрын
Yes. If you check the playlist it's in there someplace.
@getdavemoore3 жыл бұрын
I was just about to request a lesson on regex! I've tried to learn from so many sources but it's still an enigma to my brain!
@ExcelGoogleSheets3 жыл бұрын
I have videos on REGEX functions in Google Sheets on the channel. To be completely honest with you, it takes a long time to be good at writing regular expressions. After years of using it I'm still finding it confusing sometimes.
@chrilleswybrandt72623 жыл бұрын
@@ExcelGoogleSheets But they are very effective and in web analytics, you only need a subset. I benefit A LOT from regex.
@yaroslavhirnyak Жыл бұрын
very helpful, thanks. Have been using =query for long, yet did not really understand that '&' mechanics
@temyraverdana64213 жыл бұрын
Thanks, very interesting and helpful
@justinrichard98236 күн бұрын
How can you add / multiple a column from the data outside of the dataset? For example, all sales is listed from Midwest in Col7 and now I want to increase all Midwest sales from the dataset by 20%, how would I go about doing that?
@Ramkumar-kf3sb3 жыл бұрын
Nice video
@JCCarstensen13 жыл бұрын
Thanks for the breakdown!
@dominicbaiamonte9991 Жыл бұрын
Can you keep the text in column E and just reference both cell E4&B3? Actively edit the query outside the formila completely
@sadashivb51003 жыл бұрын
Pls make video how to separate data by name in different column base..
@hockeylawrence93 жыл бұрын
How would i go about doing exactly this with the multiple search criteria's but also if i wanted, being able to leave one of the boxes blank and still show a result?
@himanshusharma51813 жыл бұрын
Use subquery
@walterpaiva7193 жыл бұрын
Thanks for the video
@user-tb8bw2rf6q3 жыл бұрын
good smart
@pichit.raetai3 жыл бұрын
Thank you
@shanilka_wariyapperuma2 жыл бұрын
I was looking for this for weeks. Great work 😍❤️
@ExcelGoogleSheets2 жыл бұрын
Great!
@amolparbat64283 жыл бұрын
Had a quick question on Query ....I have data in which one column has number format as " AB12234" when I use query function it's gives put as " " can you please provide resolution for this .... thank you
@akashmv65532 жыл бұрын
Hi sir, I have one question regarding selecting multiple row values in a single column. in this data example if I want to select multiple regions then which query do I have to write please let me know. I have tried Contains, and Matches. but didn't get the correct output. Please help me with a solution. Thanks in advance.
@ExcelGoogleSheets2 жыл бұрын
OR, watch Part 1 in QUERY series.
@PhogBalaan3 жыл бұрын
What if the content of the cell after "where" is a word?
@zenlababit24723 жыл бұрын
How about looking for an equal number of variable not just numeric value?
@cdamaingoogleaccountcortex29193 жыл бұрын
You could have just use named ranges, and then you can use the variable names instantly without having to substitute them.
@ExcelGoogleSheets3 жыл бұрын
I'm not sure what you mean. These are not ranges.
@navigatormoore3 жыл бұрын
I understand: "Named Ranges" in Google Sheets is where one defines a name to refer to a cell or range of cells as in this example B3 could be named "amount" and B4 could be named "region" ...if those named cells are moved or split apart by adding new rows or columns they would always be referenced, even if moved to another tab in the workbook the variable (named range) has a global scope and can be used quickly in any cell or formula without needing to refer to the Worksheet tab name and cell (I.e. Data!B3) The Tutor is one the best in regard to Learning Google Spreadsheets, and taking the general route (I.e. for beginners) is usually the best route to teach the concept of referring to a cell that has a "value" versus a "header/label" and understanding the value types (I.e. string/text, int/number, array/bracketed csv) Not understanding the concept FIRST, could cause a person to use named ranges with mistakes like naming the label instead of the value. Ie. SUM(total_items * item_price) returns errors because it is trying to sum the label ('item' * 'price') as text instead of the value (3 * 2.99) ...all due to the user not clearly understanding the concept of referencing cells and moving to the advanced parts to quickly. But again, I understand what you mean; for I only use Googe Sheets because it does allow referencing cells (defining variables) by name otherwise I would just stick with Excel because there is no other way to fly as a programmer--especially when referring to a cell or cells that dynamically affect/update other cells. #namedranges #googlesheets
@cdamaingoogleaccountcortex29193 жыл бұрын
@@ExcelGoogleSheets heres an example: =QUERY(F:F,"select F where F < "&named_rangle_variable) named_rangle_variable is a named range of a single cell, eg A2
@ExcelGoogleSheets3 жыл бұрын
@@navigatormoore Named ranges in Excel work the same way as they do in Google Sheets in my experience.
@ExcelGoogleSheets3 жыл бұрын
@@cdamaingoogleaccountcortex2919 You could certainly do that, but that's not directly in the string. The point of substitution is to leave the query string intact. Having pretty variable names or reference names is a whole different issue.
@rssumo-o2b10 ай бұрын
Hi. can you please make an example with tabs as variable?
@leonardomoleiro41443 жыл бұрын
How could you filter for 'South' and 'MidWest`?
@cheewurz3 жыл бұрын
Wow
@masaudamin66992 жыл бұрын
Im facing with an error "Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC" The query is: =Query(pivot table J:T, "Select J, R where J is not null group by J, R order by R ASC Label 'Month'") Please help me with that !!!!
@ExcelGoogleSheets2 жыл бұрын
One thing that is obviously inaccurate is the use of group by without any aggregate function.
@user-iv1fc3es1x3 жыл бұрын
Thank you very mach
@bvbarry3 жыл бұрын
Is it possible to make the data "Data!A1:G1650" as variables? I want the same query work in different tabs Like DataA!A1:G1650, DataB!A1:G1650, DataC!A1:G1650... etc. Thanks
@ExcelGoogleSheets3 жыл бұрын
INDIRECT function
@bvbarry3 жыл бұрын
@@ExcelGoogleSheets Thank you very much
@rssumo-o2b10 ай бұрын
I'm trying to do it with the variable JUN! which i have many tabs with other months like JUL, AUG, SEP. Please someone help me. below formula does not execute it just show as it is minus the equal sign. Thanks =QUERY(JUN!E:AN,"SELECT E,F,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AJ,AK,AL,AM,AN WHERE H='CXSDA'OR H='CXSDA ENT'ORDER BY E, FALSE",2)JUN
@christopherjones29582 жыл бұрын
I'm a little lost - here's my query: =QUERY(Game_Data, "Select B where F= "&'Game Data'!F4,0) For some reason it's returning the error "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Loss" (loss is 'Game Data'!F4). Can anyone tell me where I'm going wrong? It looks like it's trying to reference the variable as if it's part of the table and it's not. Thanks!
@ExcelGoogleSheets2 жыл бұрын
=QUERY(Game_Data, "Select B where F= '"&'Game Data'!F4&"'",0)
@christopherjones29582 жыл бұрын
@@ExcelGoogleSheets Perfect tyvm
@emilianoreyes3 жыл бұрын
Thank you! Please, can you help me with batch paste data function.
@ExcelGoogleSheets3 жыл бұрын
What does "batch paste data function" mean?
@vanshshah59212 жыл бұрын
when i remove the number from b3 it gives me an value error whereas I want it to show me the whole data
@ExcelGoogleSheets2 жыл бұрын
kzbin.info/www/bejne/pH26aYabp9yhgNE
@romandeity47543 жыл бұрын
Why do we add a star after SELECT?
@ExcelGoogleSheets3 жыл бұрын
* means all columns. Watch QUERY series for more info.