QUERY Function - Variables - Google Sheets
HTML-код
- Опубликовано: 30 сен 2024
- 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
I'd never thought of using substitute in this - super smart.
Thank You!
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
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?
Keep going ❤️
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! 😏
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 🙏
Amazing work, thanks!
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!!
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 RUclips channel for any questions concerning Google Sheets.
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!
Yes. If you check the playlist it's in there someplace.
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!
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.
@@ExcelGoogleSheets But they are very effective and in web analytics, you only need a subset. I benefit A LOT from regex.
You're superman!!!
Pls make video how to separate data by name in different column base..
This was super helpful! Saved me from having to hire someone. Thank you.
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 !!!!
One thing that is obviously inaccurate is the use of group by without any aggregate function.
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?
Use subquery
Very useful in real world to make dashboard
Thanks, very interesting and helpful
You could have just use named ranges, and then you can use the variable names instantly without having to substitute them.
I'm not sure what you mean. These are not ranges.
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
@@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
@@navigatormoore Named ranges in Excel work the same way as they do in Google Sheets in my experience.
@@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.
Thanks for the breakdown!
Thanks for the video
Can you keep the text in column E and just reference both cell E4&B3? Actively edit the query outside the formila completely
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
very helpful, thanks. Have been using =query for long, yet did not really understand that '&' mechanics
Thank you
good smart
Nice video
Hi. can you please make an example with tabs as variable?
How could you filter for 'South' and 'MidWest`?
How about looking for an equal number of variable not just numeric value?
What if the content of the cell after "where" is a word?
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.
OR, watch Part 1 in QUERY series.
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!
=QUERY(Game_Data, "Select B where F= '"&'Game Data'!F4&"'",0)
@@ExcelGoogleSheets Perfect tyvm
Thank you! Please, can you help me with batch paste data function.
What does "batch paste data function" mean?
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
INDIRECT function
@@ExcelGoogleSheets
Thank you very much
when i remove the number from b3 it gives me an value error whereas I want it to show me the whole data
ruclips.net/video/nLW8SerwnJo/видео.html
Thank you very mach
Wow
I was looking for this for weeks. Great work 😍❤️
Great!
Why do we add a star after SELECT?
* means all columns. Watch QUERY series for more info.
Sr, plz share this file
docs.google.com/spreadsheets/d/10QXfX2OqIbHJNnylAuTL6-MJ0EfNADG7e75ZKq-j_F4/edit?usp=sharing
@@ExcelGoogleSheets Sr, grant me to edit this file for practice
@@usmaniqbal1836 click on file - make a copy