@@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
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.
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?
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?
Hi, I'm having issue in syncing all sheets at 15:40, all the sheet doesn't appear even I have put the { }, it shows the error saying the sheets can't be found, is it due to all my sheet having inconsistence title? Or is it because the file it previously under csv and I open it using Google Sheets?
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
Super helpful. but please do you have a video on an instance where the individual sheets are still being updated. But i still need the consolidated list to be updating without loosing data?
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 😅
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?
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")
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 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?
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
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?
can you please help, at 16:24 you mentioned where col1 is not null. in my case every thing is working fine , but when i mention "where col1 is not null" it is giving me error as parameter2 NO column : col1
Hi, since it says there's no column 1, that tells me you're using data straight from the spreadsheet without modifying it first. For example, if you're using columns A to F then try "Where A is not null"
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.
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.
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
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.
@@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.
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.
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
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
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!
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?
Hi, I have a question. If I edit on one of the workbook, will the combine workbook also update that edit? Thankiu
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?
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.
Hi, thank you for your video, may I know how I can separate each IMPORTRANGE data by one extra row?
Hi, I'm having issue in syncing all sheets at 15:40, all the sheet doesn't appear even I have put the { }, it shows the error saying the sheets can't be found, is it due to all my sheet having inconsistence title? Or is it because the file it previously under csv and I open it using Google Sheets?
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?
Thank you for simple explanation. You make me happy!
Super helpful. but please do you have a video on an instance where the individual sheets are still being updated. But i still need the consolidated list to be updating without loosing data?
This method with the array of importranges will work with data being continually added or updated.
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 😅
Did you spot the uncorrected error? It takes a good eye! Let everyone know what you saw!
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
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")
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.
Thank you very much! Very easy to follow and I liked your screen casting as well🎉
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?
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
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?
can you please help, at 16:24 you mentioned where col1 is not null. in my case every thing is working fine , but when i mention "where col1 is not null" it is giving me error as parameter2 NO column : col1
Hi, since it says there's no column 1, that tells me you're using data straight from the spreadsheet without modifying it first. For example, if you're using columns A to F then try "Where A is not null"
Excellent example and explanation. Thanks.
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
🤯 I think you just fixed a lot of issues with a project I'm working on.
Thank you so much, this help my life a lot better 😄
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
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.
Thank you very much! Very helpful!
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
Broh, You are outstanding 🎉
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 😎
Great video, really helpful
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!
Dude! thank you for this!
Happy to help, Justin
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.
Great !!!! this is cool as heck
sir how we take its total ?
Thank you - it was very helpful video
Wow! Thank you for a really helpful video
You're very welcome!
Very helpful ❤
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?
GREAT STUFF !
Game changer!!! thank you!!
Happy to help!
Super helpful .. thanks!
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!
how do I reference the sheet name on each line of the Master data?
Best to reference the url and have the name beside it
FABULOUS!!!! Thank you very much!
Happy to help!
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 ❤️
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.
thank you very helpful
Thank you, very useful.
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
Thank you so much.
You are great😊
Thank you so much 😀
awesome thanks!!!
Getting error Array_Literal
The number of columns in the sources need to be the same.
@@HashAliNZ its the same no of column
My Favourite
👍💯
Nice one ....Thank you
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