Wow this was truly helpful as I’m building out a product that incorporates multiple members to keep me updated on the status of our business. I’ve incorporated this and I must say that it work flawlessly. Every day I learn something new and I am glad that I came across your channel. Thanks again for your tutorial.
@@layymer once this is setup, it is fully automated and dynamic because of the importrange functions. To make it more dynamic, let's say by automatically adding extra tabs from files in the same folder, would require a script. A video for that process is currently in the works
I love you! Thank you for making my life easier lol I didn't want to be forced to learn how to use scripts to connect different spreadsheets together :))))) I just came here to say that.
At 7 minutes I followed how to combine sheets (which is awesome) into a master sheet & avoid the blanks....but then how can I sort the master without messing up the formulas? Exmple....then in the master I want the master sheet to always sort by dollars sold highest to lowest that it pulled from all the sheets....is that possible
Yes that is absolutely possible , in that case extend query formula at 16:24 like this query(......, "Where Col1 is not null order by Col5 desc") Here i assumed your dollar column is Col5 I hope it solves your problem
You can also do one thing that is more easy, to give your working formula to chatgpt and ask it to update that formula so that it can sort dollar column in descending order, give your column reference to it and it will give you working formula World of ai 😅
Great video thanks - as this is dynamic ie the master changes then the copy updates - is it possible to rework this so it only imports once - maybe on a button press - so it does not slow down each time a colleague changes anything in the master - but can at least make 1 / 2 copies per day?
Absolutely! At the end of the query, just add 'order by Col2 desc/asc'. For example: =QUERY({Datasets},"Where Col1 is not null order by Col2 desc") to sort by column 2 from biggest to smallest. If you want from smallest to biggest, use asc instead of desc. You can have multiple sorts in there too: =QUERY({Datasets},"Where Col1 is not null order by Col2 desc, Col1 asc")
Very Handy. For me i like the Sheet per month limiter, but you say google sheets is more effecient with pulling data from a single sheet. That seems to be a tip that underlies efficient spreadsheets. I guess the question is to broad, but how in the world is a medium user to know what is an effiecient way to set up a spreadsheet project?
Question: Using your example at 16:02 in the video, lets say I have additional data to add to each row on this master sheet after you have combined the workbooks....each time a new sheet (month/year) is added the new data in the master sheet will not correspond to the correct row. How can this be fixed?
Yep, this method does exactly that. Whenever something changes in the independent tabs or workbooks, the Master (which I labelled 'Raw Data') will automatically update
Hi, Thank you so much for your videos they are great! However, I have a question. I'm trying to combine different sheets into one master sheet, but this sheets have tabs how would and in the example that you give you don't mention that, how would that work?
Hi Silvia, Do you mean you have lots of tabs and one master sheet you want to collate everything into? As long as the structures on all the tabs are the same, you can do =query({tab1!A1:K;tab2!A1:K, tab3!A1:K},"Where Col1 is not null") Copy and paste that formula into your spreadsheet, then replace the tab names and the ranges with your needs
Hi @@HashAliNZ , thanks for responding, I have 10 sheets with different names (1,2, 3, etc..)each sheet have different tabs (a, b, c, ect..) about 6-10 each, some same name some different. I want to have a master sheet using only one of the tabs that are the same name from all the sheets. Let's say tab b is the same name in all sheets, that's the one I want to combine would it be possible with that query?
Hi! I have an app script that automatically creates new tabs based on data inputs and was wondering if there’s a more dynamic formula that is able to merge data from new tab into the master sheet as well. thanks!!
It wouldn't be fully dynamic because you'd need to reference the name of the new tab. I guess you could do it with an indirect function after listing out all the tab names.
13:30 Allowing access for each link individually takes tons of time. What I do instead is putting all the excel files into one folder, setting the folder's Sharing on "Everyone can Edit" Then the importrange() automatically has access to all of the spreadsheets Put your saved time/money here -> BE45000439748789 Communication: THX G
@@HashAliNZ I became an instant sub. I've watch countless RUclips videos but yours was the best. Thank you for sharing your knowledge. Looking forward to more amazing videos.
Brilliant, thank you very much. One question please. If I am bringing in 4 sheets and wish the sheet name (or associated text relating to that sheet) to be shown in new column after the imported data is there a way to do that? I hope that makes sense but if not: if each sheet relates to an area then I would like to import the standard columns in each of the sheets and then add the area that the sheet refers to?
Hello, thnk you for this - the query function is working, but it is not pulling all of the data from the other sheets, only some of the data. I am not sure why?
Hey Jerome, It's difficult to tell without seeing the spreadsheet or the formula. Feel free to paste your formula here and I'll see if I can spot any problems
Well, I find this super easy and I can get this done within five minutes, but I've been working on client's spreadsheets like these for years so have the experience. There is a way this can be done with a script and it's very simple if you know how to write scripts, but it'll need to be customised for your setup.
Can I change the data in the combined sheet? For example, if I have some errors in one table and I cant go to that specific table, can I do it in the combined table so the changes reflec everywhere?
Nah, make the changes to your data in the original spreadsheet. Combining all your data should be kinda like a data dashboard for reviewing information
I'm trying to combine sheets where the new data is entered in columns rather than rows but when I'm following along using what youve shown abve its just putting the next data set into new rows so throwing everything out. hope that makes sense
Hi Michael, It's generally better to add new data in rows instead of columns, but if that's not possible for whatever reason here's a fix. At 3:19 I use A2:G. For columns, you would use A2: 7, or however many rows you have. The way when a new column is added, it'll copy of the every cell from A2 down to row 7.
Really helpful video, Subscribed right away! Wanted to ask - my headings in the source spreadsheets are in rows, but I am looking to list them into columns in the master spreadsheet. Is there a way to do that? Sorry if I sound rude in any way, I have less than 12 hours and have more than 500 sheets of data, your prompt response/help would be a massive help. Thanks in advance!
Absolutely! We can use the =TRANSPOSE() function to turn the data from rows into columns. For the 500 sheets, it's best to do it with a script similar to the second half of this video, but you might need to make a few adjustments based on your specific case.
Is there a way to 'allow access' for all the sheets in one go? I had 579 sheets and it took a lot of time(an hour maybe) to allow access for every single one individually. It would be really helpful for those who work with a lot of sheets on a regular basis (if they want to combine them).
Yep! Pop all the files into one folder in your Drive, then set the permission of that file to allow access to whatever you want: view only or Edit access
Is there an easier way to copy the links of the worksheets? I have more than 500 sheets and copying their links one by one will going to take me ages! Please help!
Absolutely! Open up a new spreadsheet within the folder that has all your Google Sheets. Click on Extensions > Apps Script. Delete the default function, then paste this in: function getSSIDs() { const folderID = '1CVEzhKbeupe_6zP4uoRnMhPNcoMFUFWD'; const folder = DriveApp.getFolderById(folderID); const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS); let spreadsheetIds= []; while (files.hasNext()) { let file = files.next(); spreadsheetIds.push(file.getId()); } return spreadsheetIds; } function writeSSIDs() { let spreadsheetIds = getSSIDs(); let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.clear(); for (let i = 0; i
@@HashAliNZ can't thank you enough for this! I am glad that I stumbled upon your channel. If you ever need help related to smartphones, earphones or smartwatches just let me know, I'll help you out.
Did you spot the uncorrected error? It takes a good eye! Let everyone know what you saw!
Wow this was truly helpful as I’m building out a product that incorporates multiple members to keep me updated on the status of our business. I’ve incorporated this and I must say that it work flawlessly. Every day I learn something new and I am glad that I came across your channel. Thanks again for your tutorial.
Love it! Keep up the hard work, Michael!
14:07-14:33 is my favorite part of this video
That shocked realisation 🤣🤣
😂😂😂
@@HashAliNZ totally awesome, but how to make it auto without handle Ctrl+C/V ?
how to for combine sheets list(dynamic) of sheets just by formulas?
@@layymer once this is setup, it is fully automated and dynamic because of the importrange functions. To make it more dynamic, let's say by automatically adding extra tabs from files in the same folder, would require a script. A video for that process is currently in the works
🤯 I think you just fixed a lot of issues with a project I'm working on.
Excellent example and explanation. Thanks.
I love you! Thank you for making my life easier lol I didn't want to be forced to learn how to use scripts to connect different spreadsheets together :))))) I just came here to say that.
Always happy to help!
At 7 minutes I followed how to combine sheets (which is awesome) into a master sheet & avoid the blanks....but then how can I sort the master without messing up the formulas? Exmple....then in the master I want the master sheet to always sort by dollars sold highest to lowest that it pulled from all the sheets....is that possible
Yes that is absolutely possible , in that case extend query formula at 16:24 like this query(......, "Where Col1 is not null order by Col5 desc")
Here i assumed your dollar column is Col5
I hope it solves your problem
You can also do one thing that is more easy, to give your working formula to chatgpt and ask it to update that formula so that it can sort dollar column in descending order, give your column reference to it and it will give you working formula
World of ai 😅
Thank u very much! Because there are many people teaches how to combine 3 sheets , but no one tell them how to combine more sheets
Happy to help, Ashley!
Great video thanks - as this is dynamic ie the master changes then the copy updates - is it possible to rework this so it only imports once - maybe on a button press - so it does not slow down each time a colleague changes anything in the master - but can at least make 1 / 2 copies per day?
Yeah that's doable with a script. You could either set it up as a button or you could put a time trigger on it.
Great video, really helpful
This is great, What if we want to sort the master sheet by Date or Number ascending/descending in a specific column? can it be done?
Absolutely! At the end of the query, just add 'order by Col2 desc/asc'. For example:
=QUERY({Datasets},"Where Col1 is not null order by Col2 desc")
to sort by column 2 from biggest to smallest. If you want from smallest to biggest, use asc instead of desc. You can have multiple sorts in there too:
=QUERY({Datasets},"Where Col1 is not null order by Col2 desc, Col1 asc")
Very Handy. For me i like the Sheet per month limiter, but you say google sheets is more effecient with pulling data from a single sheet. That seems to be a tip that underlies efficient spreadsheets. I guess the question is to broad, but how in the world is a medium user to know what is an effiecient way to set up a spreadsheet project?
Super helpful .. thanks!
Thank you - it was very helpful video
Will be work to combine multiple workbooks if it have different columns?
Question: Using your example at 16:02 in the video, lets say I have additional data to add to each row on this master sheet after you have combined the workbooks....each time a new sheet (month/year) is added the new data in the master sheet will not correspond to the correct row. How can this be fixed?
hello! if for instance, I want to have an extra column to note what months these data were from, is there an easy way of doing it?
Thank you for simple explanation. You make me happy!
Thank you very much! Very easy to follow and I liked your screen casting as well🎉
Would it be possible to combine multiple sheets into one master sheet. And the master sheet auto update when those multiple sheet is updated?
Yep, this method does exactly that. Whenever something changes in the independent tabs or workbooks, the Master (which I labelled 'Raw Data') will automatically update
I followed your method but the main document is not updating. Why?
Hi, Thank you so much for your videos they are great! However, I have a question. I'm trying to combine different sheets into one master sheet, but this sheets have tabs how would and in the example that you give you don't mention that, how would that work?
Hi Silvia, Do you mean you have lots of tabs and one master sheet you want to collate everything into? As long as the structures on all the tabs are the same, you can do =query({tab1!A1:K;tab2!A1:K, tab3!A1:K},"Where Col1 is not null")
Copy and paste that formula into your spreadsheet, then replace the tab names and the ranges with your needs
Hi @@HashAliNZ , thanks for responding, I have 10 sheets with different names (1,2, 3, etc..)each sheet have different tabs (a, b, c, ect..) about 6-10 each, some same name some different. I want to have a master sheet using only one of the tabs that are the same name from all the sheets. Let's say tab b is the same name in all sheets, that's the one I want to combine would it be possible with that query?
Hi, I have a question. If I edit on one of the workbook, will the combine workbook also update that edit? Thankiu
Wow! Great tricks to cut tedious work in the end 😍
Yep - I've got some tips coming up to make it even more automated.
@@HashAliNZ ready to learn more so 😎
Hi! I have an app script that automatically creates new tabs based on data inputs and was wondering if there’s a more dynamic formula that is able to merge data from new tab into the master sheet as well. thanks!!
It wouldn't be fully dynamic because you'd need to reference the name of the new tab. I guess you could do it with an indirect function after listing out all the tab names.
Hi, thank you for your video, may I know how I can separate each IMPORTRANGE data by one extra row?
13:30 Allowing access for each link individually takes tons of time.
What I do instead is putting all the excel files into one folder, setting the folder's Sharing on "Everyone can Edit"
Then the importrange() automatically has access to all of the spreadsheets
Put your saved time/money here -> BE45000439748789
Communication: THX G
Thank you so much, this help my life a lot better 😄
This video was amazing.
Thanks so much, Eric!
@@HashAliNZ I became an instant sub. I've watch countless RUclips videos but yours was the best. Thank you for sharing your knowledge. Looking forward to more amazing videos.
Thanks for the support! I'll get them out when I can ❤️
Brilliant, thank you very much. One question please. If I am bringing in 4 sheets and wish the sheet name (or associated text relating to that sheet) to be shown in new column after the imported data is there a way to do that? I hope that makes sense but if not: if each sheet relates to an area then I would like to import the standard columns in each of the sheets and then add the area that the sheet refers to?
Sorry, I'm not sure what you're asking. I think I'd need to see an example
Broh, You are outstanding 🎉
Hello, thnk you for this - the query function is working, but it is not pulling all of the data from the other sheets, only some of the data. I am not sure why?
Hey Jerome, It's difficult to tell without seeing the spreadsheet or the formula. Feel free to paste your formula here and I'll see if I can spot any problems
Sir ur really great... Amazing teaching and tricks...
Thank you so much for very helpful video.. m big fan u sir..❤
It's my pleasure!
Thank You, So, when I do the second sicario and iportrange I get the #REF! and I get a ERROR "cannot find rang or sheet for imported range."
Never mind this comment I found my issue, I didn't understand that it had to be 'TAB' name and not any other reference
Thank you so much.
Any way you can make this even easier, especially if you have > 80 sheets?
Well, I find this super easy and I can get this done within five minutes, but I've been working on client's spreadsheets like these for years so have the experience.
There is a way this can be done with a script and it's very simple if you know how to write scripts, but it'll need to be customised for your setup.
Thank you very much! Very helpful!
awesome thanks!!!
GREAT STUFF !
Can I change the data in the combined sheet? For example, if I have some errors in one table and I cant go to that specific table, can I do it in the combined table so the changes reflec everywhere?
Nah, make the changes to your data in the original spreadsheet. Combining all your data should be kinda like a data dashboard for reviewing information
Game changer!!! thank you!!
Happy to help!
sir how we take its total ?
Thank you, very useful.
thank you very helpful
Great !!!! this is cool as heck
I'm trying to combine sheets where the new data is entered in columns rather than rows but when I'm following along using what youve shown abve its just putting the next data set into new rows so throwing everything out. hope that makes sense
Hi Michael,
It's generally better to add new data in rows instead of columns, but if that's not possible for whatever reason here's a fix.
At 3:19 I use A2:G. For columns, you would use A2: 7, or however many rows you have. The way when a new column is added, it'll copy of the every cell from A2 down to row 7.
FABULOUS!!!! Thank you very much!
Happy to help!
Really helpful video, Subscribed right away! Wanted to ask - my headings in the source spreadsheets are in rows, but I am looking to list them into columns in the master spreadsheet. Is there a way to do that? Sorry if I sound rude in any way, I have less than 12 hours and have more than 500 sheets of data, your prompt response/help would be a massive help. Thanks in advance!
Absolutely! We can use the =TRANSPOSE() function to turn the data from rows into columns. For the 500 sheets, it's best to do it with a script similar to the second half of this video, but you might need to make a few adjustments based on your specific case.
@@HashAliNZ thank you! Really appreciate your response on this
Is there a way to 'allow access' for all the sheets in one go? I had 579 sheets and it took a lot of time(an hour maybe) to allow access for every single one individually. It would be really helpful for those who work with a lot of sheets on a regular basis (if they want to combine them).
Yep! Pop all the files into one folder in your Drive, then set the permission of that file to allow access to whatever you want: view only or Edit access
SIR HOW CAN WE ADD SAME ITEMS PURCHASE AND SALE FROM DIFFERENT SHEETS TO ONE MASTER SHEET TO GET THE ACTUAL STOCK OF ITEMS ?
Use a sumif function to add together products with the same SKU
can you combine different combine multiple sheet into one,but have different column?
Sure. Just tell the query which column you want. For example
=query({Sheet1!A2:F;Sheet2!A2:F},"Select Col3 where Col3 is not null")
@@HashAliNZ like sheet 1!a2:f;sheet2!a2:g
it is possible?
You are great😊
Thank you so much 😀
Is there an easier way to copy the links of the worksheets? I have more than 500 sheets and copying their links one by one will going to take me ages! Please help!
Absolutely! Open up a new spreadsheet within the folder that has all your Google Sheets. Click on Extensions > Apps Script. Delete the default function, then paste this in:
function getSSIDs() {
const folderID = '1CVEzhKbeupe_6zP4uoRnMhPNcoMFUFWD';
const folder = DriveApp.getFolderById(folderID);
const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
let spreadsheetIds= [];
while (files.hasNext()) {
let file = files.next();
spreadsheetIds.push(file.getId());
}
return spreadsheetIds;
}
function writeSSIDs() {
let spreadsheetIds = getSSIDs();
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.clear();
for (let i = 0; i
If you need to see exactly how to extract the IDs, I made this video just for you: ruclips.net/video/-csVP1OjRCw/видео.html
@@HashAliNZ can't thank you enough for this! I am glad that I stumbled upon your channel.
If you ever need help related to smartphones, earphones or smartwatches just let me know, I'll help you out.
My Favourite
👍💯