This is the best! Other tutorials were not as concise as yours. This one clears out all the errors you may be making when using the functions! Thank you!
@benkohns14252 жыл бұрын
This is AMAZING and taught so clearly. Thank you! I'm so glad to see people solving Sheets with functions and queries instead of trying to sell me add-ons (which my company won't let me have).
@chrisk50768 ай бұрын
This is exactly what I needed! Googling and AI couldn't solve my problem but you did it perfectly!
@natamotor698910 ай бұрын
Thanks for the step by step tutorial, very very very helpfull. Cheers!
@realestatepassiveincome4 жыл бұрын
Wow! Your explanation is very easy to understand. Great job! Thank you.
@gessamdiongzon4928 Жыл бұрын
You are a lifesaver! Thank you so much for this tutorial! I might finally be able to submit my project and sleep early tonight haha You are great!
@ExcelGoogleSheets Жыл бұрын
Fantastic!
@prahalladjena16342 жыл бұрын
You explaining every video so nicely. You are so genius.
@cristianpinheiro90482 жыл бұрын
Hi. Im from Brazil. I must say your video was grate! More then that it saves a lot of work and trouble trying to learn apps scrips. Thank you so much for this great class. U earn a follower.
@ParthaDas-wy6ps Жыл бұрын
Watching your videos made my difficult task very easy, I ❤ U Sir.
@vishakhanehe65082 жыл бұрын
Awesome! You have explained very well and sorted my very big problem in which I was stuck form last 4 days. Thanks so much.
@prajwalshetty20473 жыл бұрын
Nice Video 👍
@sirajahmed20033 жыл бұрын
Thank you so mch
@gauravsehgal84044 жыл бұрын
Superb Sir 😊
@ExcelGoogleSheets4 жыл бұрын
Thanks a lot 😊
@jonathangodoy43393 жыл бұрын
You are the best bro ! thank you so much !!
@diaa22624 жыл бұрын
Brilliant 👍 thanks man i learned a lot from you and I hope you start a SQL chanal.
@ExcelGoogleSheets4 жыл бұрын
There will be SQL videos coming with SUPERSQL function.
@jigneshpatel4464 жыл бұрын
Excellent Video
@sarwadeajit3 жыл бұрын
Thanks love your work..
@MuhammadUsmanTaj Жыл бұрын
Awsome trick and its solved my issue, thanks dude
@sergheyzaporojanu6872 Жыл бұрын
this is very well put together. Thanks for the tutorial! Do you have video about linking data from different files in Excel and GSheets? I am struggling a bit with Excel since they don't have IMPORTRANGE. And I wish to know some ways to feed the live data acros different Excel files stored on OneDrive. And if there is a way to do the same between OneDrive files and Gdrive sheets, that would be so cool!
@richardmhain4 жыл бұрын
Nice, never really thought that was possible, thanks for show us how. I guess the first IMPORTRANGE can start with A1:G so we don't have to copy and paste headers. Cheers!
@ExcelGoogleSheets4 жыл бұрын
You could, but I like to keep my data separate.
@casianreport33183 жыл бұрын
the datas must be keep separate always, because you must work on them. When you sort for example, you sort with the headers, so its not nice,
@amazing3794 Жыл бұрын
Awesome 👍😎
@nhattruongphan32042 жыл бұрын
thank you very much for sharing this. It helped me a lot
@afitpacklife8591 Жыл бұрын
Thanks. That's great! But what if you have ranges you want to blend into the same rows, with a shared unique ID. For example, someone's personal info from one source, and sales figures from another source?
@Unexpectedly_Odd3 жыл бұрын
Good enough to be saved in my list! Thank you very much!
@No1Design_ Жыл бұрын
Thanks for the lesson and tips just a quick question...What if I do not need to import a certain column(s)? let say in your example the "Sales_Rep" & "state" is there a syntax to formulate in this "importrange"? Thank in advance
@purplegallery30973 жыл бұрын
Thank you so so so much sir!!! 🙏🙏🙏🙏
@joshginther75052 жыл бұрын
Thank you! Very informative!
@mei-meiyu884911 ай бұрын
This is very clearly and it works for me but i put data validation in master file very thing goes wrong.
@jkmqwerty43043 жыл бұрын
THANKS!
@Katie-264 жыл бұрын
Thank you for all your videos!! Awesome! I'd love to figure out how to create a master with sheets that do not have the same headers (e.g. Google Form populates the responses with the question as the header. Also, how the master can have the columns mixed, instead of grouped by source sheet. Thanks!
@XXY6753 жыл бұрын
This is GR8!
@christopherdeal50553 жыл бұрын
Great instructional! I have a question though. Lets say I want to combine multiple sheets to a master sheet and each sheet has tabs designating the week of the year(ie: week 1, week 2 thru week 52) and every week I am going to need a new tab in the master that pulls the data from the corresponding week in the other sheets but I don't want to have to edit every formula. Is there a way to use a cell in the master sheet for the tab names(ie week 5). As an example: I have a timecard for john in a sheet with tabs for week 1, week 2 & week 3. I also have a master sheet that pulls all employees timecards and has tabs for week 1, week 2 & week 3. somewhere in each tab on the master sheet a cell indicates the name of the tab(ie week 1) which is referenced in the formula to merge the data from John's timecard week 1. This way I can copy the tab & just change the data in that cell to "week 2" for example and the formula will now draw the data from Johns timecard tab for week 2? I hope the explanation makes sense & I greatly appreciate your input.
@imr4nkhan Жыл бұрын
🔥This is just way too cool. THANKS FOR YOUR EFFORTS. IT IS REALLY APPRECIATED especially your tips for organizing the formula via Alt Enter🔥
@dungmh3 жыл бұрын
Thank you so much!
@5202033 Жыл бұрын
thank you!
@kennethtruong9042 Жыл бұрын
How do I include hyperlinks since the hyperlink is removed automatically.
@rizkameutiarossy8758 Жыл бұрын
Very useful and makes my work much easier! i was just wondering if anyone can answer this, is it possible to do the query + import range formula (so there wont be any blank rows in combined datas) if the datas are still on going datas? (means i do this import ranges from multiple sheets to have an automatic updates from multiple sheets)
@tomotobe11 ай бұрын
may i know what is the equivoloent in Excel for this please?
@sandeep64522 жыл бұрын
Thanks!!
@reallyefficient4 жыл бұрын
Is there a way to import so that formatting of the cells matches? We use many color-coding systems and this would be super helpful!
@LookingOverYourSoul3 жыл бұрын
i think that working with app script may solve your prlbm
@sayandey25292 жыл бұрын
what if i want to import all the columns that is dynamically updated along with the rows.
@wesleypestana75732 жыл бұрын
Very cool thanks for sharing!Is it possible to add a column to the master sheet where the cell would reference which workbook the data was pulled from?
@ExcelGoogleSheets2 жыл бұрын
Yes, but it would be a very convoluted formula. This video should give you some clues kzbin.info/www/bejne/enXPmWZ7g5uBjJo
@amitbar3572 жыл бұрын
Thank You !!! Huge help.
@ExcelGoogleSheets2 жыл бұрын
Glad it helped!
@trcackett2 жыл бұрын
No comment - just a technical question Year after year I am creating a new spreadsheet with 12 sheets for every month of the year. Up to now I always made a spreadsheet copy and started to eliminate all the values of the preceding year - not the formulas of course. That's a tedious job and could not find any YT Instruction for such an easy ? question: "How to copy a new yearly spreadsheet with just the formulas, not the values ?" Any hint greatly appreciated Terence Cackett
@skipcosworld Жыл бұрын
Hey, I have a question, let's say I have A3 added from a specific 'child' sheet, in my master sheet; let's say I wanna do a change to the 'sales' tab, for row A3 in the master sheet, how can I do that change so it will reflect on the 'child' sheet without throwing "REF" error in the master sheet? Thank you
@vernavong4 жыл бұрын
i try to create data like this , but when i add more data in to the first or the second partial sheet , then the Master file do not increase the data ??? why not ???
@rasheduzzamanmukul9838 Жыл бұрын
Thanks a lot.
@ExcelGoogleSheets Жыл бұрын
👍
@johnhenry5223 Жыл бұрын
HI,..Let's say you have sales and quantity in different workbook,.how will you divide /multiply sales and quantity in a third master workbook,...
@layla1499 Жыл бұрын
Is there a way to just simply combine multiple sheets into one workbook, not merging it into one sheet? For example, if we have 3 different report in google sheet, can we just have one workbook with 3 reports but just in each tab - making it one workbook with 3 tabs? (Using g script).
@aryakn-17512 жыл бұрын
HI In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.Getting this error in final stage
@jinsen77203 жыл бұрын
thanks!
@ponnamkishore2901 Жыл бұрын
How to block cell selection in google sheet like "Microsoft excel protect lock cell option". Could you please explain the trick
@BlondeBlueyesgirl4 жыл бұрын
I an learning so much with your videos, they're really great. Is it possible to feed the partial data sheets from the master sheet? For example, imagine that I add a column in my master sheet that I want it to show in my partial sheets. I've tried to use the vlookup + importrange that you've talked about in another video and it does work but when the master is updated with new rows the column that I added in is obviously not going to match. Thank you in advance! ^_^
@ExcelGoogleSheets4 жыл бұрын
Use QUERY or FILTER functions.
@sagir31633 жыл бұрын
super !! thanks!!!
@ExcelGoogleSheets3 жыл бұрын
Thank you too!
@darelynnfung35052 жыл бұрын
Is there a way to sort columns after you use this formula? Using the filter feature, it only sorts the first data set. Thank you for this very helpful video!
@ExcelGoogleSheets2 жыл бұрын
No. You can use SORT function to autosort your data though. If you want to manually sort it, then you'll need to copy/paste values someplace.
@mateusz.ptaszek4 жыл бұрын
Would you have any information on IMPORTRANGE limitations, cause I am having issues and can not find clear information about it. Mainly how many spreadsheets can be connected to one, cause I have a MASTER spreadsheet that has all the information that I mainly VLOOKUP from but I ran in to an issue where it starts saying - that I need to have access to that spreadsheet even though I am the owner. After a bit of reading I found out that this happens when there are to many spreadsheets connected.
@ExcelGoogleSheets4 жыл бұрын
There is no clear information. And in addition to that they seem to change limits over time. Something that works today might not work tomorrow. So you just end up creating helper files in the middle to dance around those issues.
@phanmylinh322810 ай бұрын
When I update in master file, it seems like all data is gone. Can't do any changes on it. Can we have some formula that links both ways??
@pichit.raetai4 жыл бұрын
Overall Compare between Filter and Query function which one is better? Thank you.
@ExcelGoogleSheets4 жыл бұрын
Depends on what you need. FILTER is a good utility function in sheets, works best when you need the results used within a different function. QUERY is more of outer wrapper function, less likely to be used within a different function.
@romanjennings10973 жыл бұрын
Fun question at 12:12: What happens if the data above fills the 3 spaces (A17,A18,A19) and then fills the cell containing the new function(A20)? Would it A:Break the data sheet by overwriting the new function B:Auto adjust by shifting the cells If A is true, do you know a script that might fix this? Thank you for your time, I have learned a lot already!
@romanjennings10973 жыл бұрын
Oh wow, you address this 5 seconds later by using an array. Such a good teacher. Thank you!
@paigefrankovich71012 жыл бұрын
If my sheets are color coded are, is there a way to bring the colors over to the master as well?
@ExcelGoogleSheets2 жыл бұрын
No, not with a formula.
@jakubk83523 жыл бұрын
19:52 - my life with google sheets
@oOoScarecrow31oOo3 жыл бұрын
Hi, Nice work there! Is there any way to make it additive? I mean like each week I get a new file and I want to combine it with the previous data. Previous data will not need to be updated but just merge the new data from the new file.
@dhanishmehta2 жыл бұрын
Hello Nice explanation......... How do I give access as the dialogue box says learn more and Allow access is not showing...... Pls guide.
@adityakarmacharya69362 жыл бұрын
Appreciated Man! Thanks :)
@SunilKumar-le7tv3 жыл бұрын
Thanks a lot...
@hanapanda443 жыл бұрын
If a cell is larger on the original document than normal, how can you get the master to accurately depict so all the text shows?
@BeatrizSierra-vv5so5 ай бұрын
What can I do when Error: Too large Data. appears?
@mehdikrishanbutan3 жыл бұрын
I have one more query... Is it possible to know the data in the Master Sheet belongs to which Sheet as I have managed to Get all my Six (Group_1-6) files to ome Master File ... But now the problems is that non of the Six File have Name in any Header ... By which we can know the data is of which Group file... Let me know if there is any suggestions or solution to this... Thanks
@luuminhvuong4 жыл бұрын
Which is faster? Filter vs Query
@tinymillionaire97442 жыл бұрын
Hi I can online combine 2 excel sheet, but I got error on the 3rd, it says (In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.). Please help
@gam84392 жыл бұрын
how to hyperlink a sheet to another workbook?
@khaledaboelhamd3 жыл бұрын
Thank you
@richardthemagician8991 Жыл бұрын
Wonderful! Question: I am combining data and I want to sort the dates in descending order. My problem is that the sheets are from different years and it seems to be sorted by the first character in the date (the month) and not in actual chronological order. Is there a way to fix that?
@ExcelGoogleSheets Жыл бұрын
That means google doesn't recognize those as dates, it sees them as text. Try to go to each data table an format the column as date, see if that helps.
@richardthemagician8991 Жыл бұрын
@Learn Google Sheets & Excel Spreadsheets I tried that but I'll try it again.
@kikelomoakinsehinwa71473 жыл бұрын
Hi when the files are updated with new data will the master file automatically update??
@mahyalrefaey231611 ай бұрын
when it is showing error there is no allaw access choice
@inah9653 жыл бұрын
thanks a lot what i almost seen in the video was just consolidating...what if the excel file had existing data included but i need to copy other details from other sheets..( they have same template, encoded by different people ) thank you.
@ExcelGoogleSheets3 жыл бұрын
I think you might need VLOOKUP function, but it's not exactly clear what you're trying to do.
@anhnhatnguyet46282 жыл бұрын
If there are many files (eg 100 files) with many sheets of the same structure in the same folder, merge data into 1 sheet of 1 file, using Google Script, is it possible?
@kikelomoakinsehinwa71473 жыл бұрын
Help pls! I combined 4 sheets but now on the master sheet there’s one column that has mainly dates but there are some text/string in that column. The only data that’s shown are dates but the text fields within the column are blank. Do you have a video on this??? How can this be solved? In the case of mixed data types what to do? Pls help
@Tech4Vikas4 жыл бұрын
Thank you this helped a lot. I have one query though. To grant permission you wrote formula for each separately first, granted permission and then worked on it. It's ok for 2-3 sheets but I'm going to work on around 400-500 sheets and this will become all the more tedious than the project already was. Is there a workaround for granting permission? I'll be accessing google sheets form different accounts. Hope I'm clear with the problem I'm facing. Do let me know otherwise? Thank you so much again.
@ExcelGoogleSheets4 жыл бұрын
There is no workaround that I know. Without user specifically granting access, IMPORTRANGE doesn't work. Also I don't think Google Sheets supports 400 importrange functions in one sheet anyways. You can try something like this as alternative kzbin.info/www/bejne/qp6uoqWvaap-mrM
@Tech4Vikas4 жыл бұрын
@@ExcelGoogleSheets Hey, Thank you so much for your reply. So the exact number is 304 (blocks in 33 districts); so that will be around 9-10 importrange formulas per sheet, so I think that shouldn't be a problem. The issue is what you also covered a bit at 3:58 in this video. That I have to get permission one by one (getting permission isn't a problem but the method looks tedious). Also gave a quick look to the alternate video; it looks awesome but it is just a tad bit heavy for a guy from non-coding background and also if it will fit in my plan. I was earlier going to use your this video and the one with pivot table in multiple sheets to get my work done. Will update you how I finally did it and share if possible. and please know that you're a hero. Hero we don't deserve but definitely the one we need. All the best. Thanks again.
@ExcelGoogleSheets4 жыл бұрын
@@Tech4Vikas I'm afraid you will have to allow each sheet separately.
@krishnatate4765 Жыл бұрын
Sir please help me, how to sum only sale column??
@ruyoofalnafea83463 жыл бұрын
No one can see this edit or use it except me ? I need this edit to appear to people who are using this sheet ! Please i need answer
@MiguelHernandez-oe8tl4 жыл бұрын
Thank you for your videos!. How could I get the selected item in a drop-down list in google form?
@ExcelGoogleSheets4 жыл бұрын
I don't think you can.
@piaskarmokar9763 Жыл бұрын
Dear sir. Can you help me with e google sheet document? I'm stuck.
@jonashilpert68954 жыл бұрын
Have you already made a video about two way sync? I would like to link two sheets in both ways (so whenever i update one sheet it should automatically update the other one and reverse)?
@ExcelGoogleSheets4 жыл бұрын
No, I have no video on this. This would require bunch of scripts and it would also be a permission nightmare. Can't be done with a formula.
@michaelaustinealvarez75784 жыл бұрын
Hello, Could you make a video on how to get the User's email address when they add a data to the sheet? and put it in a function or add it in a button which has a different function.. That would be an amazing topic for your subscribers like me.. Thanks a lot and keep on posting..
@ExcelGoogleSheets4 жыл бұрын
There are only certain cases when you are allowed to get user's email. Generally speaking, unless the user authorizes a dialog to allow to get their info, you will not be able to get that info. This however can be a little different if the users are the part of the same domain in G-Suite account. In those cases you simply use var email = Session.getActiveUser().getEmail(); Logger.log(email);
@michaelaustinealvarez75784 жыл бұрын
@@ExcelGoogleSheets I guess it should work regardless of User Auth, because I was trying to put it in a specific Users. They should authorize it no matter what. Btw, thanks for the reply. Keep it up and more power.
@centrallabpeshawar81393 жыл бұрын
May I get the file containing the formula?
@LookingOverYourSoul3 жыл бұрын
Could anybody tell me what's the difference betweeen this way and the Combine Data from Multiple Google Sheets Documents (Workbooks) in Google Drive into One Workbook? I mean, which are the things that one allows and the other doesnt?
@mani2care4 жыл бұрын
Really nice work and need to know about if the different headers what will do for the combine different worksheet or work book is there any script or formulas pls assist
@ExcelGoogleSheets4 жыл бұрын
This should help you kzbin.info/www/bejne/eYfOqpl9irh7m8k
@mani2care4 жыл бұрын
Learn Google Spreadsheets it’s nice try but my question if the sheet different headers and values what will be the option to combine automated
@mersihaceranic67624 жыл бұрын
Hello, thank You for awesome video. I want to combine multiple Google Sheets with different columns (use some of the columns). Is there any way to do this?
@ExcelGoogleSheets4 жыл бұрын
It's possible, but it's very messy and complicated.
@ExcelGoogleSheets4 жыл бұрын
This video has the overall idea how to do it and you need to combine it with bunch of importrange functions kzbin.info/www/bejne/eYfOqpl9irh7m8k
@mersihaceranic67624 жыл бұрын
@@ExcelGoogleSheets Thank You so much for the reply :)
@sairanganadhaddanki93603 жыл бұрын
i am trying this but its showing as "Adding permissions" with loading symbol...but its not ...can you please let me know what am i doing wrong
@davidfernandezbajo4 жыл бұрын
Thank you for sharing your knowledge, Any chance considering a video about passing oauth 2.0 to access third party api data through app script?
@ExcelGoogleSheets4 жыл бұрын
Not in the near future.
@leonardsimatupang68043 жыл бұрын
THank you master
@gestionappartement1945 Жыл бұрын
Bonjour, merci pour votre aide , comment faire une jointure entre plusieurs feuilles ?
@EEZYEEEE3 жыл бұрын
I liked your voice work on Despicable Me
@agekecaly5503 жыл бұрын
Kindly help I'm getting an error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
@George-ep4ed4 жыл бұрын
Great explanation. Meanwhile, do you have an idea of how to push excel data into google sheets, thank you
@rahul-globalsmartservices37834 жыл бұрын
Great explanation. Meanwhile, do you have an idea of how to push excel data into google sheets, thank you . I would love to get a solution too.
@ExcelGoogleSheets4 жыл бұрын
kzbin.info/www/bejne/gqiklqGLgLyqbsk
@rohemoriyama3 жыл бұрын
What is you have 100 client workbooks that you want to combine together into one master?
@inah9653 жыл бұрын
hi how can i consolidate multiple worksheets without affecting the existing row /details in the final sheets.,coz i have this reports that i need to consolidate files from different people ( same excel sheet details).need to combined in one file ..without deleting or affecting other data per row.
@ExcelGoogleSheets3 жыл бұрын
It sounds like you may need to use scripts.
@user-kq4vq6rk2m11 ай бұрын
how can we do this using app script
@kazubit4 жыл бұрын
I'm try use query() formula at 10:07 minute in this video and change "SELECT * WHERE Col1 is not null" to be "SELECT *" for intent to get all consolidate record but the consolidate sheet return only DataSet1. Have you any advise?
@ArturKovalchuk3 жыл бұрын
You imported all the cells, including empty. If You scroll down, You'll see data from other table as well. That is why it is better to use 'is not null'