Google Sheets - QUERY from Another Sheet, IMPORTRANGE, Use Multiple Tabs, Subquery Examples Tutorial

[@bobmolnar8591]

This entire series is amazingly helpful. It is THE definitive guide to using Sheets like a pro. Thank you!

[@philchua3049]

Agree! I wish this was in my search results 2 years ago

[@aikslf]

QUERIES ARE WONDERFUL!! I was wondering how to reference a series of cells from a different worksheet into a series of cells in a different worksheet without copy-pasting a formula a million times. This is the answer. You don't have to type it all out manually, just as expected. Thanks for these tutorials, I looked for google spreadsheet tutorials and this series is what I came across, which is helpful.

[@aikslf]

and here's a tip: to select only one column, enter this range: A: A or whatever column you want, with its name on both sides of the ":" symbol. The left side of the column is the start point and left side is end point, so you could do something like A12:A too.

[@abbashaidari8313]

I don't have enough words to appreciate you putting out all this amazingly explained information for free on KZbin... Thank you...!!!! Thank you....!!!!! Thank you....!!!

[@socialstyledancelessons6548]

Your videos are the best. I have been able to build an entire database in multiple sheets because of your awesome tutorials. THANK YOU!

[@phillipbrantley7534]

I’ve searched months and found this great video to solve my problem.

[@ricardochaverra2002]

Thank you!!!, I was banging my head against the wall previously because IMPORTRANGE wasn't working for importing multiple sheets into a single query, you sir are awesome!! thanks again

[@nickieoakwood6926]

Awesome my main man. Had a bit of panic at first not working but I had forgot the QUERY at the beginning. All great.

[@nancylalicon4192]

This is amazing and your talent of explaining the steps inspires me!

[@gsxmentor]

Sir, you are the Best, your videos have contributed too much in me gaining respect in my office

[@allanandersen9514]

Thank you so much! I have been able via your videos to minimize manual updates. Thanks again.

[@nasyakoyna]

I'm learning new tricks...This tricks also helpful for learning SQL queries..Thank you brad, very concise and clear..

[@Richard.bassan]

Greetings from Brazil. I would like to thank you for this serie, it was very helpful

[@aprendendogooglesheets8040]

Esse é o melhor professor do KZbin!!!

[@noriskandarbinazmi8262]

thank you , for great sharing

[@anshul4541]

Thank you so much for this informative video. I have been looking for this for a long time. it solved my biggest problem.

[@ClarenceMatthewUrot]

This is what I have been looking for. Thank you very much. Easy to understand and follow.

[@Allen-L-Canada]

Wow, the subquery part is something I was looking for a long time. Since Google Sheet doesn't offer the "HAVING" SQL clause, this subquery is essentially the substitute for the "HAVING" clause.

[@ExcelGoogleSheets]

Correct 👍

[@Allen-L-Canada]

@@ExcelGoogleSheets this is so useful that I have enhanced my spreadsheets in multiple places today. Thank a lot brother!

[@domanhhung2010]

That is an amazing lesson, and you are Talent online teacher.

[@calitran8803]

Thank you SO much - been having so much trouble sorting text data and this finally solved my problem at work!!

[@hemantshinde4857]

VERY USEFUL FOR BOTH BEGINNERS AND PROS

[@satisfieduser2696]

great job with your channel, data is king

[@akarabdallatyf8422]

pretty awesome helpful and useful to the highest levelz

[@MrChiluby]

This was so helpful. Literally the only place I found such a clear answer. Great video

[@BruiserBailey1]

Thanks for all your tutoring. Your videos are just what I needed as I needed to brush up on my accounting reporting skills not utilised since I retired years ago. The software environment has changed a lot and the advantages of Google sheets offers as a way of communicating and collaborating is significant. I used to use Visual dBase which had an SQL like query language so the syntax is familiar. What was great about VdB was that you could input data in a user friendly form and see the code come out on a "Command Screen". It was a great way for a novice to write and learn to code. In addition you could copy and paste the code and incorporate into larger programs. Wouldn't it be great if Google Sheets could come up with something similar?

[@findthetruth3021]

This is very fruitful and productive video that I have ever seen on KZbin. I want to to do the same thing you did but Instead of just taking the same info from sheet 1 to the master list, I need to cut the information form sheet one to sheet two while I apply query. Why I need this and ask you that because I have an Google sheet and there is a column which is just for the status of the projects in which I have pending and completed. So, whenever it is going to be completed in the sheet 1, then I want it to automatically delete the completed project in sheet one and transfer it to the master list. I will appreciate that if you can help me in this regard, not only me need this but all most all of us need to have it. A huge thnx for you and your amazing and productive videos. Best KZbin channel ever.

[@findthetruth3021]

Could you please answer my question?

[@Nishakumari-bx7zi]

Helpful 👍

[@JanJanuszNosacz]

Thanks for the tutorial on using QUERY with IMPORTRANGE. Really helped me!

[@joanaamorfajardo3056]

This video solved my biggest problem. Thanks and more power!

[@ExcelGoogleSheets]

:)

[@joanaamorfajardo3056]

@@ExcelGoogleSheets what if you need to filter more than 1 state, instead of just IL, how do you do that?

[@ExcelGoogleSheets]

Covered in part one of these series.

[@amarstrums7205]

Thank you very much sir. Your video has helped me a lot.....

[@jawsstech]

Wow, the IMPORTRANGE explanation at 9:58 is going to be a game changer!

[@infinityvaservices]

you explain it so well. thank you.

[@robevans2114]

Excellent. Well done

[@foxhoundv1210]

Wonderful! I knew there had to be a way to query from multiple sheets, I just didn't know about the bracket for the array.

[@aminaehikioya3694]

Thanks so much for posting this video! any tips on using importrange where the source file has URLs in multiple columns that you would like to show & work in the destination file?

[@nagarjunatutika2002]

I like the way you teach about google sheet and its functions, please help me to pulldata from one google sheet to other tik by tik

[@jdgh86]

Excellent video, thank you!

[@VishalKumar-km4sj]

Awesome tutorial! Loved it. Thanks for putting in such efforts to educate folks.

[@ExcelGoogleSheets]

Glad you liked it!

[@MouhamadAlem]

This is great. Many thanks.

[@TheXervex]

Good evening, First of all thank for this awesome video! I learn how to do the importrange and combined it with a query from another video to add a filter on it. I am working in logistics in a production environment and making a tab to check each production status. For instance a tab with "Nesting" when they are collecting the materials. This will come in the "nesting" tab. But during a weekly follow-up meeting I add comments in a column next to the import range. When the "nesting" than is being put on "finished product" the imported line dissapears (which is my intention), but the comment I wrote myself just stays in the same cell and thus is 'transferred' or 'linked' to the "nesting" row below. When a status changes and dissapears how can I also let the comment dissapear with it? Is this possible? Thanks in advance!

[@dionyomangpang608]

Thank you its really helps me in query function.

[@gurpreetkaur-zr1qj]

Ty for this video GOD BLESS U😊

[@lapneto]

@ExcelGoogleSheets What if I have it more dynamic, referencing each of the importrange in 4 cells, one each. If one is blank (as it may be), is there a way to handle the error (similar to what iferror does)? My sheet still imports the same data (or behaves funny) when I delete one of the ranges... thanks for the help! Your videos are always very good and didactic!

[@aNDy-qh1em]

Thank you so much sir, thank you so much, sir. Your tutorials ones of the best because of being halpful and very clear explanation (English is not my native) ! My questions is: do you need to do subquery because there is no 'having' statement in the 'google sql' ?

[@ExcelGoogleSheets]

yes.

[@ge2893]

You are a WONDERFUL teacher, Thank You!😊

[@ambercarvell1980]

You are a great teacher. These videos are super helpful.

[@newlearning001]

Its a great Video and help alot in my reporting... i have too much data like 15000 line with Columns goes upto BS. getting error Result too large how to rectify this?

[@angelv1393]

Thank you so much, exactly what I am looking for 😊👍👍👍

[@JerichoRally]

Pure gold!!!

[@markuswinter-cdps3008]

Thanks so much for these videos! What I have yet to find is a video that addresses querying the same tab/sheet from hundreds of different worksheets. So, my situation is such that I have a running list of reports for a calendar year. By the end of the year, there are 400 or so reports. Within the reports, there is a equipment2 sheet that I am trying to query with a range from B4:E28, where the results can be either 1 row, or all 25, depending on how much equipment was used for that given incident (potentially no rows, if no equipment was used). I have a script that imports all the URLs from the reports into a tab/sheet that is called ReportURLs. Column D houses the URLs. So, how can I query ReportURLs!D2:D dynamically to pull out all the data? Thanks!

[@fingerprint8479]

Hi, great tutorial. Can this query syntax be used from a script? Have you got some examples to indicate? Thanks

[@cherrepeltier4266]

Great demo. Is there a way to retain the formatting from your Master sheet onto your new sheet so it looks like the Master Sheet?

[@AndersonMenezesTutorials]

Your video is really amazing. But I have a questions. I'd like to set up on Google Sheet where I can search just typing initials, such as "P" in a cell and them query makes the information in my sheet appear. Such as you've done with Shirt. Could you help with the funcion formula?

[@SingingLi]

Thank you for your sharing. I learn a lot from this video.

[@maziarkarimi4383]

If pulling from another URL, should it still be A2:H or can it be A1:H? Thanks for the great videos.

[@daviddoget1773]

You all are awesome. Thank you for the great videos you create and share!

[@vortexstaff6269]

hi..can u pls help me coz, after i the formula when i enter doesnt extract my data only showwd is the headers..thanku

[@shraddhamahajan4894]

While pulling data from another spreadsheet with multiple tabs, can I specify the number of the tab instead of the name of the tab in the importance function? Basically, the source sheet from where I need to pull data from will have multiple tabs but I want to keep the first tab as the default source of the data, no matter what the name of the tab is. Data needs to be pulled from just one specific cell, say A1.

[@Nikzangelo]

Hi there. Using this formula,Is it possible to do multiple filter in the same column?

[@DatosEficientes]

Great vid... thx. I can't find the limit of the number of tabs you can query with { }. I think I'll have to look at some scripts since my file will keep growing daily with new tabs that will need to be referenced. Thx again.

[@kautsarzulkafli9400]

Hi Great videos, the explanation is clear, but can I do the editiing on the data that I have imported to new spreadsheet?

[@ExcelGoogleSheets]

No.

[@Scobietube]

Thanks for your help

[@arindammajumder3095]

Is it possible to filter both IL & CA at the same time during query and importrange?

[@jonathanzuaiter9273]

These video are super helpful as the steps are explained very well. I have tried various formulas to meet what I need and while I can make each formula work on it's own, i'm having trouble finding the right combo. I have two workbooks. I need one workbook to vlookup / match a name in a column that is on another workbook. Example, John Doe appears in Coulmn H in 1 workbook named WRAP. I need the formula to search column B in another workbook Named Assets, to see if that name is true. If it is True, then it should highlight the name in WRAP workbook a color, using conditional formatting. Ideally, once John Doe has been removed from Assets workbook the Wrap workbook will remove the highlight. Sorry for the long comment. Is there another way to submit this. Would love your help!

[@grivello]

level 40 google sheets! Thank you.

[@Bael114]

Great Presentations. Would you share the sheets data so we can practice?

[@palyamreddisekhar3475]

This was so helpful. But when i'm using query(importrange) function to get data from one sheet to another sheet data is not fetching all the time. some times it is given error like #value. May i know solution here with google script or something. Thank you so much in advance!🙂

[@SteveHutcheson]

Really good, very clear tutorial

[@salmanhaider19]

Wow Brother, thanks for making life Easy

[@TVPSSSKKampongBahru]

Thanks.. it really help very much..

[@annaandfabiano]

Amazing. Thanks. Is there a short cut for chromebook to use the Dollar $ to lock the cell?

[@ExcelGoogleSheets]

No F4 on Chromebook?

[@annaandfabiano]

@@ExcelGoogleSheets unfortunately, nope. Even if I try to use an external keyboard, it does not work with the same functionality

[@focusmediumsolutions1734]

Thanks for the awesome video. How can we overwrite importange spreadsheet data from destination sheet so source sheet data will be changed ?

[@diarmuidhartley3074]

What is the boundary limit you mentioned? Could I use 50-60 import ranges for example

[@teamsandleadership2418]

Very clearly explained - thank you!

[@jamesturner237]

I'm running into the same problem. "At 18:49-18:50 in the video, there is an error message that pops up saying "In ARRAY_LITERAL, an Array was missing values for one or more rows." Are you able to explain what that means, and how to fix it?"

[@mercyfrs]

Thanks a lot, great learning..so how I getting this files , its better to practice.

[@eldaddy427]

Hi there, seems excellent and what I need but it doesn't work for me as my "IL" criteria is more diverse.. I need to look within the celle for one word (people answered paragraphs) can we please exchange on this issue? that would be very kind . Dave

[@traktornusantara889]

You sir, really gave me a lot of new knowledge, Also I would like to ask you a question related to database, In my case, I have multiple tabs in my spreadsheet, and I combine them into a big database using Query function, now that I have master database using query I'd like to add more column in master database that would also be added in my tabs or my mini databases, The problem is, the database using query function is dynamic, meaning that the data would grow as the data from others tabs increasing, so the column where I wrote in my master database is not valid anymore as the data is changing. Is there anyway how to put the data to follow the dynamic change by the query function? I'm really sorry for my bad english, thank you

[@bidyutchakraborty2433]

Thank you for nice series on Google query and other advanced techniques. I have a problem regarding Google Finance. Hope you can give me proper guidance. The problem is that I wish to compute Weekly, Bi-weekly and Monthly gains by a list of stocks. Google finance gives 'changepct' for daily data. But for weekly data it doesn't. I have made a formula using query to compute weekly percent change. But for Bi-weekly I have not yet succeeded. Can you suggest any formula for bi-weekly and monthly gains of stock prices. May be I am missing some where some thing.....

[@ExcelGoogleSheets]

How did you do the formula for weekly percent change?

[@bidyutchakraborty2433]

@@ExcelGoogleSheets Thanks for your quick response. As you asked, let me explain. There is a provision in GoogleFinance to list the weekly prices of a stock. With that I made a formula using queries, which gives the desired result. The process is a bit cumbersome. Here how i did it. Using query from GoogleFinance weekly price list - sorted in desc order, i) I extracted the first price (using Limit 1)-say A, II) extracted sum of first two prices (using Limit 2)-say A+B. Knowing price A and sum A+B, it is just use of algebra to get A-B [2A-(A+B)]. This will give latest weekly price change. Then computing % change is easy. This resulted in a large formula. And let me inform you that I have also computed the bi-weekly and monthly percent changes. But these all are large formulae and prone to make mind boggling mistakes while constructing it. Hence I wanted to know how to select the price using the GoogFinance output where the first column is date. I have seen your video on extraction of data based on date limits. But that is not working with in line Google Finance data, neither as a date or number. This would have made the life easier. Can you suggest something on this problem? The answer is a bit long, but then I have to explain any way.

[@ExcelGoogleSheets]

Yea, I'm afraid my solution wouldn't be simple either.

[@bethylancione9722]

This is very helpful, but I another question. I have used the Query ImportRange function to move data from one sheet to another but my SELECT WHERE is referring to a specific date. I.e. I want to move all data where the date is 5/1/2020 and later. When I use the formula you suggested it works but all of my information is showing up on one cell block. How do I fix this?

[@peterbyrne4770]

Thanks for the information. Super helpful! Is it possible to utilize these, and have the data become static? I'd like to manually add data into my spreadsheet, but I don't want to to be overridden by the importrange data.

[@ExcelGoogleSheets]

This will be an active connection. You'll need to use scripts instead.

[@AprilSheree17]

Thank you, this was so helpful!

[@nishayates3049]

Thanks for these videos - they are incredibly helpful. Is there a video to show how to combine data from 2 different sheets where the number of columns and the data is different but what would allow a join is that there is an email id for the records on both sheets. I want to create a sheet that would allow me to select columns from each of the sheets where there is a join for the email id. Is this possible?

[@WonderMonkey2]

Appreciate the examples!

[@Mosdefin8ly]

Great video. Thanks so much. Is there anyway to filter/sort each column of imported (importrange) data in the master?

[@ExcelGoogleSheets]

If I understand it right then yes. Check out part 1 of QUERY function it will show you how to do it.

[@Raidon88]

Great video, extremely useful! Thanks!

[@Success.simplified_]

!!!! I used importrange function to copy a range from one sheet to another and I made some column in that sheet which were filled manually. how can i fix the manually/static filled data with the automatic/dynamic filled data?

[@ayoubelghouas3188]

i have the same probleme bro

[@karanmadan6403]

with the importrange query.. i want to use formula " select B,C,D WHERE B= " CELL REFERENCE FROM CURRENT SHEET "... HOW DO I DO THAT ?

[@normankong4679]

Thanks, with the query how do we add the TAB "name" to each row so we know which TAB the match came from

[@longphan2865]

Very useful mate, your video helps me a lots

[@MVanDFamily]

Fantastic! Quick question, what If i want the query for more than one option. So in your example I want to show for IL and CA at the same time. How would you do that?

[@ExcelGoogleSheets]

kzbin.info/www/bejne/mIiZgWWPq7GskMk

[@johnn4314]

Wow amazed.. thanks again man.

[@SuppanReviews]

Awesome TUT man

[@findthetruth3021]

It is an amazing video and very useful, but how about moving all the rows instead of copying from sheet1 to sheet2. I mean, is there any way to move all of the data from sheet 1 to sheet 2 instead of copying. For instance, in sheet1 we have data 1,2, & 3. Yes, we can use query and import range functions to transfer them into sheet2. Meanwhile, there are still the same data of 1,2, & 3 in the sheet1 as well. But, I want to move the data from sheet1 completely (No data to be there), and having them only in the sheet2 (having the data there only, 1,2 & 3). Many thanks for your amazing and fruitful videos.

[@DatosEficientes]

Hi.. you will most likely need to use scripts (macros) in order to tell Sheets to transfer all the data in one go... otherwise you'd have to cut/paste it yourself everytime. :)

[@talebbagazi1435]

Hi, I have a question, I apply this formula =QUERY('Master Data'!$A$1:$T,"Select E,F,T Where T = 'City Name",1) as you can see from the formula how i can add more city but its in the same master data sheet?

[@romanmakarov2021]

Could you please say what word to use when I need to query information not from a column (Col1/Col2/...), but from a line (which are designated as '1', '2', '3'). I have a page with columns often changing their positions, so I need to query a value from (line2) if (line1) contains 'specific-word'

[@amandatowslee6195]

Wow! Super helpful! Here is my issue if you could help please. What if I want to query where h = "IL" and "CA"? How do I get results for multiple rather than just one?

[@ExcelGoogleSheets]

h = "IL" OR h = "CA"

[@amandatowslee6195]

@@ExcelGoogleSheets Thank you!!!

[@ExcelGoogleSheets]

Sure. If you go to the channel I have a playlist covering query function that should cover pretty much anything you need.

[@shanekatsoolis8434]

So Very Helpful and Succinct! I would appreciate knowing if there is a way to use an item on a Dropdown List (MASTER BEDROOM), as the QUERY? As in.... =QUERY(SELECTIONS!$A$1:$N$201,"SELECT A, B, C, D, E, F WHERE D = 'MASTER BEDROOM'",1) but replace 'MASTER BEDROOM' with the cell reference $A$3 that is a Dropdown List Also is there a way to "SELECT all columns, so complete rows of data are returned if they contain MASTER BEDROOM in column D? Cheers!

[@ExcelGoogleSheets]

kzbin.info/www/bejne/pH26aYabp9yhgNE

[@foodandy1020]

This is really good video. How can we combine 2 sets that the column are different. For example, the first sheet contains date, products and revenue. The second sheets contains date, products and expense?

[@davidbretey527]

I would love to have help on this too.

[@yteng2]

@@davidbretey527 A solution I found is to use the smaller set to bring in product (unique id) and the variables from the larger set. And using the vlookup to bring in the rest data from the larger set. E.g. =ARRAYFORMULA(VLOOKUP(A2:A,IMPORTRANGE("sheet id","sheet name!B:AR"),{2,3,5,42,43},false))

[@siraizuddin]

Awesome!!

[@jaimemcqueen4367]

Thank you!!!! So easy to follow and understand :D

[@ExcelGoogleSheets]

Glad it was helpful!

[@aprudencio]

Given a column of spreadsheet URLs and possibly next to each a range I. E. Transactions!A2:b30, is it possible to automatically pull and join the data from all those sheets?

[@aprudencio]

I had to use google script so that when a new URL and range is added, it builds the formula to join all the importrange formulas inside a query formula based on the URL list

[@andreistroca]

Great tutorials!