How to merge several Google sheets into one without copy-pasting

Every once in a while each Google Sheets user faces the inevitable: combining several sheets into one. Copy-pasting is tedious and time-consuming, so there has to be another way. And you're right – there are several ways, in fact. So get your tables ready and follow the steps from this article.

All the ways I describe can be used to process large tables. But to keep this guide as clear as possible, I'll keep my tables short and am going to cut down to a couple of sheets.

Reference cells in Google Sheets to pull data from another tab

The easiest way comes first. You can pull entire tables to one file by referencing cells with data from other sheets.

Note. This will do if you need to merge two or more sheets within one Google spreadsheet. To merge multiple Google spreadsheets (files) into one, jump right to the next method.

So, my data is scattered all over different sheets: June, July, August. I'd like to pull data from July and August into June to have one table as a result:

  1. Find the first blank cell right after your table (the June sheet for me) and place the cursor there.
  2. Enter your first cell reference. The first table I want to retrieve starts from A2 in the July sheet. So I put:

    =July!A2

    Note. If there are spaces in your sheet name, you must wrap it in single quotes like this:

    ='July 2022'!A2

    This immediately replicates whatever lies in that cell: Enter your first cell reference below the table.

    Note. Use relative cell reference so it changes itself when copied to other cells. Otherwise, it will return incorrect data.

  3. Make sure the cell with the reference is selected and click on that little blue square at its bottom right corner. The mouse cursor will turn into a big black plus sign. Keep the mouse pressed and drag the cursor to as many columns to the right as you need to fill them with new records:
    Copy cell references to other columns.
  4. Select this entire new row, click that little blue square once again, hold and drag your mouse down – this time to fill entire rows with cell references and bring new data from another sheet:

    Bring data from another sheet with cell references.

Though this is probably the first way you may think of to pull data from another tab, it's not the most elegant and quick. Luckily, Google prepared other instruments specially for this purpose.

Copy the tabs into one spreadsheet

One of the standard ways is to copy the tabs of interest into the destination spreadsheet:

  1. Open the file that contains the sheet(s) you want to transfer.
  2. Right-click the first tab you need to export and choose Copy to > Existing spreadsheet:
    3. Copy the tab into an existing spreadsheet or a new spreadsheet.
  3. The next thing you'll see is the pop-up window inviting you to select the spreadsheet. Browse for it, click on it to highlight it, and press Select when you're ready:
    4. Locate the spreadsheet to import data into.
  4. Once the sheet is copied, you'll get a corresponding confirmation message: A message confirming the sheet has been exported.
  5. You can either hit OK and continue with the current sheet or follow the link called Open spreadsheet. It will instantly get you to another spreadsheet with the first sheet already there: The tab is copied to another spreadsheet.

Export/import sheets

Another way to import data from multiple Google Sheets is to export each sheet first, and then import them all to a necessary file:

  1. Open the spreadsheet that contains the sheet you'd like to pull the data from.
  2. Make the sheet of interest active by selecting it.
  3. Go to File > Download > Comma-separated values (.csv): Download the sheet as .csv

    The file will be downloaded to your computer.

  4. Then open another spreadsheet – the one you'd like to add the sheet to.
  5. This time, pick File > Import from the menu and go to the Upload tab in the Import file window:
    6. Upload the sheet you downloaded earlier.
  6. Hit Select a file from your device and find the sheet you've downloaded just now.
  7. Once the file is uploaded, you'll see a window with additional options for importing the sheet. To add the contents of that another sheet after your existing table, pick Append to current sheet: Adjust the options to import sheet.

    Tip. Among other settings, feel free to specify the separator and convert text to numbers, dates, and formulas.

  8. As a result, you'll get two sheets merged – one table under another: The second table has been appended to the first one.

    But since it is a .csv file you need to import, the second table remains formatted in a standard way. You will have to spend some time formatting it as you need.

Google Sheets functions to combine data from multiple spreadsheets

Of course, it wouldn't be Google if it didn't have functions to merge data in Google Sheets.

IMPORTRANGE to import data from multiple Google sheets

As the name of the function suggests, IMPORTRANGE imports data from multiple Google spreadsheets into one sheet.

Tip. The function helps Google Sheets pull data from another document as well as from other tabs from the same file.

Here's what the function requires:

=IMPORTRANGE(spreadsheet_url, range_string)
  • spreadsheet_url is nothing else than the link to the spreadsheet from where you need to pull the data. It must always be put between double-quotes.
  • range_string stands for those cells specifically that you need to bring to your current sheet.

And here's the pattern I follow to import data from multiple Google Sheets using IMPORTRANGE:

  1. Open the spreadsheet from which you want to pull the data.

    Note. Make sure you have at least viewing access to that file.

  2. Click the browser URL bar and copy the link to this file right till the hash sign (#):
    3. Copy the link to the spreadsheet of interest.

    Note. You'll need this URL even if you're going to combine sheets from the same file.

    Tip. Though Google says the function requires the whole URL, you can easily get by with a key – a part of the URL between /d/ and /edit:

    ...google.com/spreadsheets/d/XYZk0274gRlmluCTfMbzbMQWKiAeq1va77X4/edit

  3. Return to the spreadsheet where you want to add the info to, enter the IMPORTRANGE to where the borrowed table should appear, and insert the link as the first argument. Then separate it from the next part with a comma:
    4. Paste the URL into the formula.

    Note. Remember, the link should be surrounded by double quotes.

  4. For the second part of the formula, type in the name of the sheet and the exact range that you want to pull. Confirm by pressing Enter.

    Note. Wrap the second argument in double quotes as well:

    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/XYZk0274gRlmluCTfMbzbMQWKiAeq1va77X4/edit","May!A2:D5")

  5. Though the formula looks ready now, it will return the #REF error from the start. That's because the first time you try to pull data from some spreadsheet, IMPORTRANGE will ask for access to it. Once the permission is granted, you will easily import records from other sheets of that file.

    Click the cell with the error and press that blue Allow access prompt:

    6. Connect the sheets to combine them together.

    Note. By allowing access, you let the Sheets know you don't mind any existing or potential collaborators on this spreadsheet accessing data from another file.

  6. Once the formula connects to that other sheet, it will import data from there:

    7. Note. IMPORTRANGE doesn't pull the formatting of the cells, only values. You will need to apply formatting manually afterwards.

    Import data from multiple Google Sheets with IMPORTRANGE.

    Tip. If the tables are rather big, just allow some time for the formula to pull all records.

    Note. The records returned by the function will be updated automatically if you change them in the original file.

Google Sheets QUERY to import ranges from multiple sheets

And thus, without haste, we've come to the QUERY function once again. :) It is so versatile that can be used in Google spreadsheets to combine data from multiple sheets (within the same file) as well.

So, I want to merge three different Google sheets (from one file): Winter 2022, Spring 2022, and Summer 2022. They contain the names of all employees who became best in their jobs in different months.

I go to the first sheet – Winter 2022 – and add my QUERY right under the existing table:

=QUERY({'Spring 2022'!A2:D7;'Summer 2022'!A2:D7},"select * where Col1 <>''")

Let's see what it all means:

  • {'Spring 2022'!A2:D7;'Summer 2022'!A2:D7} – are all the sheets and ranges I need to import.

    Note. The sheets should be written in between the curly brackets. If their names contain spaces, use single quotes to list the names.

    Tip. Separate the ranges with a semicolon to pull data from different tabs one under another. Use commas instead to have them imported side by side.

    Tip. Feel free to use such infinite ranges as A2:D.

  • select * where Col1 <>'' – I tell the formula to import all records (select *) only if cells in the first column of the tables (where Col1) are not blank (<>''). I use a pair of single quotes to indicate the non-blanks.

    Note. I use <>'' because my column contains text. If your column contains other data type (e.g. date or time, etc.), you need to use is not null instead: "select * where Col1 is not null"

As a result, two tables from other sheets have been consolidated into one sheet one under another: Google Sheets QUERY – import ranges from multiple sheets.

Tip. If you'd like to use Google Sheets QUERY to import ranges from multiple separate spreadsheets (files), you will have to implement IMPORTRANGE. Here's a formula to pull your data from other documents:

=QUERY({IMPORTRANGE("XYZk0274gRlmluCTfMbzbMQWKiAeq1va77X4","Mar-Apr-May!A2:D6");IMPORTRANGE("XYZahJZHSlhMGLSW_xA6ZBqNmt1I0ADo4N4M","Jun-Jul-Aug!A2:D4")},"select * where Col1<>''")

Tip. I use the keys from URLs rather than entire links in this long-enough formula. If you're not sure what that is, please read here.

Tip. You can also use QUERY to merge two Google sheets, update cells, add related columns & non-matching rows. Check this out in this blog post.

3 quickest ways to merge multiple Google sheets

If standard ways of Google spreadsheets to combine data from multiple sheets seem dull, and the functions scare you off, there's an easier approach.

Combine Sheets add-on

This first special add-on – Combine Sheets – was designed with a single purpose: import data from multiple Google sheets. It's clever enough to recognize the same columns in different sheets and bring data together accordingly if you need.
Combine Sheets working scheme.

All you are to do is:

  1. Select sheets or entire spreadsheets to merge and specify the ranges if necessary. The possibility to do a quick search in Drive makes this even faster.
  2. Choose how to pull the data:
    • as a formula. Mark the checkbox called Use a formula to combine sheets if you want to have a master sheet that will dynamically change based on your original contents.

      Although you won't be able to edit the resulting table, its formula will be always linked to the source sheets: edit a cell or add/remove entire rows there, and the master sheet will be altered accordingly.

    • as values. If editing the resulting table manually is more important, ignore the above option and all data will be combined as values.

    Extra options are here for fine-tuning:

    • join records from the same columns into one column
    • keep the formatting
    • add a blank line between different ranges to notice them right away
  3. Decide where to place the merged table: new spreadsheet, new sheet, or in a location of your choice.

Here's a quick demonstration of how I combined my three small tables with the add-on:
Import data from multiple Google sheets into one with Combine Sheets.

Of course, your tables can be much bigger and you can merge lots of different sheets as long as the resulting spreadsheet doesn't exceed the 10M cell-limit.

Tip. Make sure to check out the help page for Combine Sheets or watch this 3,5-minute tutorial:

One of the options this add-on offers is to add more sheets to your previously combined data. In this case on step 1, you need to pick not only the data to combine but also the existing result. Here's how it looks:
How to add data to the existing result.

Consolidate Sheets add-on

Consolidate Sheets is a relatively new addition to our add-ons. Its main difference from the aforementioned tool is the ability to add up data in columns in Google Sheets (or rows, or single cells, for that matter).

Consolidate Sheets also recognizes common headers in all the Google sheets to join, even if they are in the leftmost column and/or the first row. There's always an option to merge Google sheets and calculate cells based on their place in the tables.

Let me break it down into steps for you as well:

  1. Select sheets to consolidate. Import more files from Drive if necessary straight from the add-on.
  2. Pick the function to consolidate in Google Sheets.
  3. Choose the way to add up cells in Google Sheets: by labels (header labels, left column labels, or both) or position.
  4. Decide where to place the consolidated data: new spreadsheet, new sheet, or any specific location within the opened file.

Here's how this process looks:
Google Sheets: pull data from another document and calculate at the same time.

There's also an option to consolidate all your sheets using a formula. This way your result will change in sync with the values in the source sheets: Use a formula to add up data from multiple sheets.

Note. There are some peculiarities you need to know about how the formula works. For example, if you consolidate from multiple different files, there will be an extra step to connect the sheets for the IMPORTRANGE in use. Please visit the instructional page for Consolidate Sheets for these and other details.

Or here's a short tutorial about the add-on work:


I truly encourage you try the add-on on your data. You'll see for yourself how much extra time you'll have after incorporating this tool to your daily work.

Merge Sheets add-on

There's one more add-on worth mentioning. Though it merges only two Google sheets at a time, it couldn't be more useful. Merge Sheets matches records from the same column in both sheets/documents and then pulls related data from the lookup sheet/document into the main one. Hence, you always have an up-to-date spreadsheet at hand.

There are 5 straightforward steps:

  1. Select your main sheet.
  2. Select your lookup sheet (even if it's in another spreadsheet).
  3. Choose columns where matching records may occur.
  4. Tick of the columns with records to update.
  5. Tweak any additional options that will help you merge two sheets and achieve the best result possible.

If these words don't speak much to you, here's a video tutorial instead:


If you're ready to try it for yourself, visit this help page for details about each step and setting.

On this note, I'm going to finish this article. Hope these ways of pulling data from multiple different sheets into one will be of use. As always, looking forward to your comments!

Table of contents

192 comments

  1. Thanks for the article. Query+{} worked well for me

  2. Hello !

    Thanks for your work.

    I'm looking for a solution if you may help me please.
    I have 2 tables that have same columns but not necessarly same data. I need to combine those tables with unique match (need to have one unique table that contains all the unique data without duplicates).

    Do you have any idea how to solve this ?

    Thank you for any help you can provide !

  3. Thanks fo your help in advance.I want to copy a sheet from one collagues sheet.i can do it with importrange , however if i want to change anything on my sheet (which was referring to my collagues sheet) all data is being erased .How can i solve this issue? basicaly i want to open copy of her sheet in my sheet and do some changes and update without effecting original sheet .
    is there anyway for that?
    regards
    Rasim

    • Hi! I think you can copy this tab into your spreadsheet as described in the second paragraph in the article above. If this is not what you wanted, please describe the problem in more detail.

  4. I have a Google sheet which has several tabs, each with a simple To Do List for my clients, e.g ClientA, Client B, Client C. There's a completed tick box, Client name, task and due date. I have used a query to combine the data from each Client To Do list into one Master To Do List. On the individual lists, I tick when a task is completed and have the sheet sorted so that completed tasks are at the top then the rest are ordered by due date. I want to sort the Master list so that it is also sorted in the same way, so that I can easily see the due dates across all clients - whereas it is showing Client A completed, then due; Client B completed then due; Client C completed then due (and so on...) Is there any way I can display the combined list of due tasks in due date order?

  5. Can you tell me how to sort the queried data?

    • This is the query formula I used....

      =QUERY({'CLIENTNAME'!A2:J38;CLIENT2NAME!A2:J37;'CLIENT3NAME'!A2:J37;CLIENT4NAME!A2:J137;CLIENT5NAME!A2:J37 },"select * where Col1 is not null",)

        • I popped that in the end and it didn't work. Can you see what I have done wrong?

          =QUERY({'Client'!A2:J42;'Client0'!A2:J37;'Client1'!A2:J37;Client2!A2:J137;Client3!A2:J38 },"select * where Col1 is not null","select A,B,C,D,E,F,G,H,I,J order by C")

          • Hello Lesa,

            Please consider sharing an editable copy of your spreadsheet with your formula with us: support@apps4gs.com. Please include the example of the result you expect to get.
            If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
            I'll look into it and try to help.

            Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

            • Ok I shared it.

              • It's called "Copy of all projects - sample "

              • Thank you for sharing the file, Lesa.

                I've adjusted your formula, please take a look:
                =QUERY({Client1!A2:J42;Client2!A2:J37;Client3!A2:J37;Client4!A2:J138;Client5!A2:J38},"select * where Col1 is not null order by Col3 desc")

                If you need the ascending order, just remove the 'desc' at the end. I highly recommend looking through our QUERY tutorial since it includes formula examples so you could see the correct way to write formulas.

              • Thank you so much!!!
                What a great help you have been !

  6. Hi Natalia,

    Just wanted to thank you for this article and helpful tips. Merging sheets - it is exactly what I’ve been looking for. Helps a lot.

    Thanks again,
    Nikolai

  7. Hi Natalia,

    I am having difficulties extracting data from 2 different google sheet workbooks into 1 (new) masterfile that would have 2 drop downs for categorizing the needed data. Would you be able to help me out?

    • Hello Tuhin,

      The IDs in your last sheet are formatted as text, while in other sheets they are numbers. Since you use QUERY, you should know that if there are mixed data types (e.g. numbers and text) within a column, the QUERY function will return only the majority data type leaving the rest as empty cells.
      Hence, you need to make sure the data in ID columns across all combined sheets are formatted the same: as numbers.

  9. How would we use IMPORTRANGE if we dont know the exact cell id ?
    We want to get the data from another sheet from the cell with the same cell id.
    It's clear using specific cell id but on copy/paste you have to correct it.
    We tried using as cell ID ADDRESS(row(),cell()) but we get #REF! error.

  10. Dear Natalia Sharashova,

    I would like to monitor the workflow time scale of workflow. I need to add an extra date column or data will save in another separate Spreadsheet based on User Status and Developer Status in the sheet. For example, if a user raises a new ticket in user status that will be recorded in a new spreadsheet with the date in the same ticket no user changes the user states it recorded in the next column with a date as the same developer status also. For your reference here I share my sample sheet URL. Kindly do need full

    https://docs.google.com/spreadsheets/d/1wBheKbbMIJtM3zlmbWqOTIftsgttp0cH1K_7HKY5-VI/edit#gid=0

    With Thanks and Regards,
    Manikandan Selvaraj.

    • Dear Manikandan Selvaraj,

      I'm sorry, I don't have access to your spreadsheet. To grant us access, please open the file, press the Share button at the upper right corner of Google Sheets and enter support@apps4gs.com. Please also make sure that your spreadsheet contains the example of the result you'd like to get. It is of great importance as it gives us a better understanding than any text description. Once you share the file, please reply to this comment. I'll look into it and try to help.

  11. While using Merge sheet/ combine sheet/ summery sheet add on , can I get source cell background color in master sheet or only cell values will be synchronized ?

    • Thank you for your question, Jenis.

      As for Merge Sheets, if you paste the results to a new spreadsheet, the colors of the original main table will be pulled as well. Yet, the add-on doesn't overwrite the colors of the main sheet with the colors of the lookup tables.

      As for Combine Sheets, there's a special option for that – preserve formatting. However, it's not available if you combine using a formula, since formulas in Google Sheets don't support formatting.

      Consolidate Sheets, on its turn, doesn't pull formatting because it creates one aggregated report from multiple different tables and each of them can have its own formatting.

      Anyways, all add-ons offer fully-functional 30-day trial period. You can test them out and see if they work as you need.

  12. Please guide me to arrange combined data alphabetically

  13. Tank's, it was really helpful. straightforward & very easy to follow.

  14. Dear Natalia Sharashova,

    I need to import only last Sunday to Saturday or (Last 7 days) newly added data from main sheet to another sheet and this function will repeat every week to extract weekly report is there any formula to do this kindly do needful.

    With Thanks & Regards,
    Manikandan Selvaraj

    • Dear Manikandan Selvaraj,

      For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) including an example of the result you'd like to get. The result sheet is of great importance and often gives us a better understanding than any text description.

      I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don’t monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I’ll look into your task and try to help.

  15. Dear Natalia Sharashova,

    is there any function key to show the automatic last update of the google sheet in a specific column when the user updates their google sheets?

    with Thanks & Regards,
    Manikandan Selvaraj

    • Dear Manikandan,

      I believe it depends on what update you'd like to see exactly (time stamp of the last edit or the values from particular columns themselves). Could you please specify?

  16. Dear Natalia Sharashova,
    Thanks a lot for your valuable help its working as per our need. In this file, there is another issue when we use some of the QUERY IMPORTRANGE to pull the data from the main sheet to user sheets some of the rows are not filling. For further details pls check the user 1 sheet in column date some column dates are not filled automatically. (for your access I shared the file in support@apps4gs.com)
    With Thanks & Regards,
    Maniikandan Selvaraj

    • Hello Manikandan,

      According to the QUERY documentation, "In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values."

      In your main sheet, there are cells with dates formatted as text. You'll find them if you double-click each cell. Those where a calendar appears are formatted as dates, those without the calendar are formatted as text. That's why they don't show up in the users' sheets - they are a minority data type.

      Fix the format and they should appear in the users' sheets.

  17. Dear Natalia Sharashova,

    I have created a process management google sheet (Main sheet) which is handled by the manager to allocate work to the freelancers.
    I have used “IMPORTRANGE” “QUERY” “FILTER” functions to pull only specific columns (ex.:Col1, Col3, Col4, Col9) to show to the specific freelancer (Name of the freelancer), like these I have created 5 different sheets for individual freelancer (User sheet).
    In the main sheet, there is a column called STATUS OF THE PROJECT (ex.Col9) which is to be filled by the freelancer in the user sheet. Is there any function or formula to change the status in the user sheet which is to be reflected in the main sheet?
    Note: We have tried to include IMPORTRANGE function in the main sheet to pull the status from the user sheet. Unfortunately, the function does not work as expected. If a user changed the status of Row 5 in the user sheet, which is not exactly changing the status of the project in the main sheet.

    With Thanks & Regards,
    Manikandan Selvaraj

    • Dear Manikandan,

      If I understand your task correctly, you pull Col9 from the Main sheet to each User sheet. The users can't fill this column on their sheets because it is returned by the formula. If they try and enter anything in Col9, it will break the entire formula. Also, you can't enter another formula into Col9 in the Main sheet and refer to the same Col9 in other users sheets because that would cause circular dependency.

      So, to solve your task, I'd advise you to avoid pulling Col9 from the Main sheet, add a status column on each user sheet manually, and then collect the required data from this column to the Main sheet.

      • Dear Natalia Sharashova,

        Thanks for the valuable information,

        As per your advice, I create a status column for each user sheet so the user can change or update the status of the project. But there is a problem the project assigned for each user is shuffled in the main sheet so individual users can see only the allocated projects.

        For example project in serial no 3 in the main sheet will be allocated to one of the users as project serial no 1, when the user changes the status of his project serial no 1 in his user sheet (Row 1) it will change the status of project serial no 1 in the main sheet not in an exact project serial no 3.

        with thanks & Regards

        • Dear Manikandan,

          For me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) including an example of the result you'd like to get. The result sheet is of great importance and often gives us a better understanding than any text description.

          I kindly ask you to shorten the tables to 10-20 rows.

          If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

          Note. We keep that Google account for file sharing only and don’t monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

          I’ll look into your task and try to help.

          • Dear Natalia Sharashova,

            AS per your requirement, I send the supporting spreadsheet for further query testing to your (support@apps4gs.com) mail id.

            WITH THANKS & REGARDS,
            Manikandan Selvaraj

            • I've got the files, Manikandan, thank you.

              However, I can see the same project numbers in each User sheet as they are in the Main sheet. The number don't change as you describe they would. Project 3 in the Main sheet is still project 3 in the User 3 sheet.

              • Dear Natalia Sharashova,

                We have reversed 1 step backward for better understanding. We got a problem with project status, not worried about serial no.

                In the main user sheet, there is a column called Project status. That is changed by each user. I need a way how a user can change status in the main user sheet via user sheet.

                With Thanks & Regards,
                Manikandan Selvaraj.

              • Manikandan,

                I've looked into your files once again. I've adjusted the Query the way I suggested earlier and added the Project Status column manually in the User 1 file (you'll need to do the same in other User files).

                I also added the formula to K2 in your Main sheet. It will check the user in column I and will go to the required spreadsheet for the status of that project.

                Hope this solution suits you.

  18. I have a sheet that is connected to a form. When people fill out the form, it populates in the sheet correctly. I have that sheet connected to another sheet. We use this form for people requesting to make reservations for a part of our building. The sheet that I have connected is supposed to copy the info from the form response sheet and then I have added columns for us to record when we contact the person back, etc. Everything works great, except this keeps happening---the people keep filling in the form and it goes to the first sheet correctly. It doesn't immediately appear on the second sheet though. For some reason, the cells show connected, for example A1, A2, A3, A4 and then it will go to A6. When I come in each week I keep having to reset it so that it goes A5, then A6, etc. So, I'm filling in the missing cell. At some point after that, it goes away again. Next week I will come in and find A1, A2, A3, A4, A6 again. Why won't my cell pull stick? Why does it keep skipping a row? How can I fix it so that it will stay fixed? Thanks!

    • Hello Kim,

      How do you pull records to your second sheet? Do you use some of our add-ons to pick up the data? Or do you refer to the first sheet with some manually-built formulas? Which ones?

      • I use

        =('THIS TAB DOES NOT NEED USED'!A21)

        in the cell

        The first sheet is called THIS TAB DOES NOT NEED USED--it is the info directly from the form. I had to call it that because my chief kept going in and changing things there instead of where he was supposed to be. :)

        • Thank you for replying, Kim.

          You see, when someone fills in the form, their responses don't go into empty rows on the response sheet. They appear as new inserted rows.
          Here's an example: suppose that the last response in your sheet is in row 20. Your formula on the second sheet is ready to pull the next data whenever it appears in row A21. But the response doesn't fall there. It is being inserted as a new row above that 21st row. Your 21st row becomes 22nd, and your formula adjusts itself automatically.

          I'm afraid I don't know for a way to stop formulas from auto-adjusting themselves, it's a standard behaviour for spreadsheets. But you can try and wrap all your cell references into INDIRECT, e.g. =INDIRECT("'THIS TAB DOES NOT NEED USED'!A21")

  19. Hello,

    I have a simple question without a simple answer I am assuming.

    I have a number of dynamic sheets that I want to consolidate into one sheet. Also I want to capture any new data that is added. So if any of the numerous sheets has a new row populated in it, it will automatically get populated in what I am calling my dashboard sheet. Basically consolidating and updating several differant sheets into one. Anytime a new row is added to any number of sheets it populates the next available row in the dashboard sheet. Almost like any new data is appended to the dashboard sheet. Any suggestions.

  20. Hello. Tell me how to create a database in GS? I want to collect rows that will be unique based on two columns. For example, if data enters table 1 (it is dynamic), then new rows are written in table 2. If data gets into table 1 again and they are already in table 2, then the record is not made. If there are fewer records in table 1, then records in table 2 do not disappear.

    • Hello Vince,

      You can use a formula like this:
      =SORTN(Sheet1!A2:D100,9^9,2,Sheet1!B2:B100&Sheet1!C2:C100,FALSE) - where B & C are columns with duplicates. However, this formula will also sort your rows alphabetically. If you'd rather avoid that, then use our Remove Duplicates add-on and its scenarios instead.

  21. How do you take several sheet (tabbed at the bottom of the google sheet) and make them into one group of 4 or 5 sheets. Basically you click on this one tab and there are "sub-tabs" that contain different spreadsheets within it. I can not figure out how to do this and I know there has to be a way. Please help

  22. hi I want to link new entries in multiple sheets at the bottom of the master sheet. pls help

  23. Hi,

    I would like to use a dynamic value for query function using indirect reference. Please have a look at the below formula:
    AK1 = Template!A13:AI50
    AK2 = 'Template (2)'!A13:AI50
    AK3 = 'Template (3)'!A13:AI50

    =query({indirect(AK1)},"Select * where Col1 is not null",1)

    The above formula works, and I would like to concatenate the ranges using a formula and replace the indirect with all the above ranges. Please advise how do I do that? I tried using concatenate combinations and I am not arriving at a solution.

    • Hi Antony,

      I mentioned a way to concatenate ranges in QUERY in this part of the blog post. It should look like this:
      =QUERY({INDIRECT(AK1);INDIRECT(AK2)},"...")

      Also, if you put 'Template (2)'!A13:AI50 into a cell, your spreadsheet will "remove" the first single quote as it's used to treat the entered value as a text. So to keep the references correct for INDIRECT, make sure to put two single quotes: ''Template (2)'!A13:AI50

  24. I created a master sheet using IMPORTRANGE; however, I want to use and edit the master sheet rather than shuffling through the original worksheets. Is there a way to now edit and make changes to the master sheet after combining worksheets?

  25. Hello, I'm trying to combine about 100 sheets in a spreadsheet with:

    =query({'SWE 21/01/21'!A2:G4;'SWE 19/01/21'!A2:G12;'SWE 17/12/20'!A2:G15;'SWE 15/12/20'!A2:G13;'SWE 3/12/20'!A2:G12;'SWE 1/12/20'!A2:G11;'SWE 26/11/20'!A2:G14;'SWE 24/11/20'!A2:G13;'SWE 19/11/20'!A2:G16;'SWE 17/11/20'!A2:G16;'SWE 12/11/20'!A2:G14;'SWE 11/12/20'!A2:G16;'SWE 5/11/20'!A2:G18;'SWE 3/11/20'!A2:G22;'SWE 29/10/20'!A2:G17;'SWE 27/10/20'!A2:G22;'SWE 20/10/20'!A2:G20;'SWE 15/10/20'!A2:G17;'SWE 13/10/20'!A2:G20;'UI/UX 9/10/20'!A2:G13;'SWE 8/10/20'!A2:G19;'UI/UX 7/10/20'!A2:G18;'DSCI 7/10/20'!A2:G7;'SWE 6/10/20'!A2:G17;'UI/UX 2/10/20'!A2:G14;'UI/UX 30/9/20'!A2:G14;'DSCI 30/9/20'!A2:G9;'SWE 29/9/30'!A2:G23;'DSCI 28/9/20'!A2:G10;'UI/UX 25/09/20'!A2:G23;'SWE 24/09/20'!A2:G23;'DSCI 23/09/20'!A2:G9;'UI/UX 23/09/20'!A2:G15;'SWE 22/09/20'!A2:G23;'DSCI 21/09/20'!A2:G5;'UI/UX 18/09/20'!A2:G16;'SWE 17/09/20'!A2:G21;'UI/UX 16/09/20'!A2:G18;'DSCI 16/09/20'!A2:G9;'SWE 15/09/20'!A2:G17;'DSCI 14/09/20'!A2:G9;'UI/UX 11/9/20'!A2:G21;'SWE 10/09/20'!A2:G26;'DSCI 9/09/20'!A2:G9;'UI/UX 9/09/20'!A2:G13;'SWE 8/09/20'!A2:G28;'DSCI 7/9/20'!A2:G6;'UI/UX 4/9/20'!A2:G20;'SWE 3/09/20'!A2:G22;'DSCI 2/9/20'!A2:G8;'UI/UX 2/09/20'!A2:G19;'SWE 1/09/20'!A2:G26;'DSCI 31/08/20'!A2:G9;'UI/UX 28/08/20'!A2:G19;'SWE 27/08/20'!A2:G27;'DSCI 26/08/20'!A2:G9;'UI/UX 26/08/20'!A2:G18;'SWE 25/08/20'!A2:G25;'DSCI 24/08/20'!A2:G9;'UI/UX 21/08/20'!A2:G22;'SWE 20/08/20'!A2:G25;'DSCI 19/8/20'!A2:G6;'UI/UX 19/8/2020'!A2:G19;'SWE 18/8/20'!A2:G25;'DSCI 17/08/20'!A2:G7;'UI/UX 14/8/20'!A2:G20;'SWE 13/08/20'!A2:G27;'UI/UX 12/8/20 '!A2:G16;'DSCI 12/8/20'!A2:G8;'SWE 11/8/20'!A2:G25;'DSCI 10/8/20'!A2:G11;'UI/UX 7/08/20'!A2:G21;'SWE 6/08/20'!2:24;'UI/UX 5/08/20'!A2:G19;'DSCI 5/08/20'!A2:G10;'SWE 4/08/20'!A2:G22;'DSCI 3/08/20'!A2:G12;'SWE 30/07/20'!A2:G21;'UI/UX 29/07/20'!A2:G24;'DSCI 29/07/20'!A2:G11;'SWE 28/7/20'!A2:G23;'DSCI 27/7/20'!A2:G10;'UI/UX 24/07/20'!A2:G20;'SWE 23/07/20'!A2:G27;'UI/UX 22/07/20'!A2:G25;'DSCI 22/07/20'!A2:G10;'SWE 21/07/20'!A2:G25;'DSCI 20/07/20'!A2:G10;'UI/UX 17/07/20'!A2:G22;'SWE 16/7/20'!A2:G27;'DSCI 15/7/20'!A2:G11;'UI/UX 15/7/20'!A2:G25;'SWE 14/7/20'!A2:G27;'DSCI 13/7/20'!A2:G12;'UI/UX 10/7/20'!A2:G23;'SWE 9/7/20'!A2:G27;'DSCI 8/7/20'!A2:G11;'UI/UX 8/7/20'!A2:G22;'SWE 7/7/20'!A2:G29;'DSCI 6/7/20'!A2:G11;'UI/UX 3/7/20'!A2:G29;'SWE 2/7/20'!A2:G29;'UI/UX 1/7/20'!A2:G23;'DSCI 1/7/20'!A2:G11;'SWE 30/6/20'!A2:G27;'DCSI 29/6/20'!B2:G11;'UI/UX 26/6/20'!A2:G20;'SWE 25/6/20'!A2:G27},"select * where Col1 is not null)")

    but it keeps telling me "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows" what could be the problem?

    • Hello Ayo,

      This error usually occurs whenever there is a blank sheet or a sheet with only a header row among your original tables. Will you be able to check that?

  26. Hi, i want to apply IMPORTRANGE only once, and not to keep the destination sheet in sync with the source sheet, How can I do that?

  27. Hello -
    Seeking Vertical calendar tracking of events across 4 tabs.
    Goal -
    "combined data" tab where : column A is date (static), column B is day of week (static) and then C, D, E, and F are each data pulled from tabs.

    For example : data would show in this format....

    1| Date | Day | XYZ-(tab a) | XYZ-(tab b) | XYZ-(tab c) | XYZ-(tab d) |
    2| 11/15/2020 |Sunday | [blank] | Mail | [blank] | Jacob |
    3| 11/16/2020 |Monday | Winner! | [blank] | Dice | [blank] |
    4| 11/17/2020 |Tuesday | [blank] | Post | Cards | Susan |

    When I have tried to use IMPORTRANGE it wants to move horizontally and Query wants to pull all data and stack.

    I am trying to pull all actions from the same date on the same row..

    Is this the incorrect function to be using? (I am more familiar with Excel - is there a Vlookup or Hlookup that would work better?)

    Thank you for ANY help you can provide

    • Hello Sunday,

      For me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) including 4 tabs you want to combine AND an example of the result you'd like to get (the result sheet is of great importance and often gives us a better understanding than any text description).
      I kindly ask you to shorten the tables to 10-20 rows.
      If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don’t monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I’ll look into your task and try to come up with a formula.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)