Comments on: 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. Continue reading →

Comments page 3. Total comments: 205

  1. Hi, is it possible to import every Nth Cell from another Sheet. I am trying to import every 6th cell from Column C. I’ve tried doing this but I keep ending up with the imported data appearing every 6 rows. I then tried to sort the range this compiled the data but also alphabetised it which I don’t want. Thanks!

    1. Hello Abi,

      Assuming there's a header that you don't need to count, please try this formula and copy it down the column where you want to pull every 6th cell:
      =OFFSET(C$1,(ROW()-1)*6,0)

  2. Hi Natalia,
    I am a novice with google sheets, I have 8 separate. How do I sheets that I want to put into one file with 8 tabs, not sure how to do this without changing the look of each page.

  3. Hi Natalia,
    Thanks for sharing this good tips.
    However, i would like to know if this is possible:
    I have one Workbook contains all the my clients' info. However there will be 5 different people in charge of the different clients according to the region. So this workbook has 6 different tabs: Master Sheet; P1; P2: P3; P4 ; P5.
    My question is:
    How can I auto-populate the data in Master Sheet tab from each sub tabs (tabs P1 - tabs P5)?
    I know how to work on the other way round (Master sheet into sub tabs).
    Can you help me with this please?
    Thank you! =)

    1. Hi Kim,

      Sorry, I'm a bit confused by your question actually :) The ways of transferring data between sheets are the same, you just need to pick one based on your exact goal.
      Are you trying to paste all tables one under another? If so, I'm afraid you'll have to do that manually (even with the add-on you'll have to run it each time) since the ranges on single tabs may expand and overlap anything below when combined. If that's not what you mean, please try to be more specific, I'll do my best to suggest the solution.

      1. Hi Natalia,
        Sorry for the confusing you.
        I was trying to create a CRM via the google sheet.
        We have 5 people responsible for different region clients. I will name them P1 - P5.
        And in the google worksheet, we will have 1 Master Sheet tab (which has ALL clients information) and another 5 tabs for each person respectively.

        The question is:
        How do I auto-poupulate the data on the Master Sheet tab while I input in the individual tab?

        1. Thank you for replying, Kim.

          To bring the info from all 5 tabs into one and make sure all new rows from those P1-P5 appear on the Master sheet automatically, I'd advise you to use one of these functions. You will still have to build a formula manually on the Master sheet so it starts working. For example, your QUERY may look like this:
          =QUERY({'P1'!A:Z;'P2'!A:Z;'P3'!A:Z;'P4'!A:Z},"select * where Col1 ''")

          I used A:Z instead of specific ranges to make sure all new records will appear.
          Please note that you won't be able to make changes to this summary table since it will be returned by a formula. To be able to edit it, you'll have to convert your formula to values or use add-ons to bring all tables to one sheet.

          1. Hi Natalia,
            Thank you for your reply.
            I will try it out and play around the worksheet a little. =)

  4. Hi Natalia,
    I'm trying to use Importrange for the first time. I get the error "You don't have permissions to access that sheet." I have just created both sheets and own both of them. They are both shared with 1 person (as a result of having been created inside a folder I own and have shared with her). What am I missing? Thank you!
    =importrange("https://docs.google.com/spreadsheets/d/1QWnXBCOF9YKq5GoroET9kCCTy9_wnhKysY5c6R5AJ6g","List!A2:G")

  5. How can I make sure that the notes or comments are also included if I'm using a =IMPORTANTRANGE formula?

    1. Melanie,
      unfortunately, Google Sheets doesn't offer this functionality at the moment. IMPORTRANGE cannot return comments and notes.

  6. QUERY and IMPORTRANGE did what I was trying to do, except the data from the two sheets stays separate if I try to sort it. So if I want to sort "sheet1" and "sheet2" by "date", the data displays as dates for sheet1 in order and then dates for sheet2 in order. Is there a way to make them intermingle?

    1. Hello Nicole,

      When using QUERY, you should put sorting directly to the formula. Please read here (Sort data with Query) how to do that correctly.

      Also, if you use two different formulas to bring the data, consider combining them into one formula. Otherwise, each new formula will sort only its contents.

  7. This works perfectly with a total of about 1700 records retrieved from the 4 data sources.
    But if I include a data source with a range of 12,000 rows, it returns # N / A
    Error
    The query has been completed with an empty result.

    I do not think such a large number of rows, especially because it is only 8 columns wide.
    Data sources are imports of other spreadsheets made using IMPORTRANGE (), since only the relevant columns are imported. Could that be why it is difficult to work with that many records?

    =QUERY({'Destinatarios Importados 1'!A2:H;'Destinatarios Importados 2'!A2:H;'Destinatarios Importados 3'!A2:H;'Destinatarios Importados 4'!A2:H};"select * where Col1 ''")

    1. Alfredo,

      yes, IMPORTRANGE can take some time returning data, especially when you refer to 4 different ranges in one formula at the same time. If ranges don't load up, try to change the condition to the one below:
      "select * where (Col1 is not null)"

      If you're still unable to make it work, please consider sharing your file (with support@4-bits.com) along with your source data and the formula that doesn't work.
      Note. That email is for file sharing only. Please do not email there. Once you share the file, just reply to this comment.

      I'll look into it and do my best to help.

  8. This doesn't work
    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.

    How should be the syntax to put a range on the side of the other? Replacing ";" for "," does not work.
    Could you give an example?

    1. Hello Alfredo,

      {'Spring 2019'!A2:D7;'Summer 2019'!A2:D7} - here I used a semicolon to put ranges one under another.
      To put them one next to each other, it should be {'Spring 2019'!A2:D7,'Summer 2019'!A2:D7}

      If it's still doesn't work for you, perhaps, your locale requires different separators.

      1. Hello, I use commas and it works. However there is a column gap (atleast 8 columns) How to fix this one?

  9. Hi,
    this seems pretty useful, but I'm looking for something slightly different. Perhaps you can provide info on how to do this?

    I want to have several spreadsheets, all accessible to ONE person to edit them. Then, I want one spreadsheet which combines all the data from the other spreadsheets. The trick is, that the amount of rows per spreadsheet can vary. So the ranges would be dynamic.

    Is it possible to do this, while getting a read-only table which contains ALL info, without white spaces?

    So if spreadsheet 1 gets more rows, they go in seamlessly into the read only, with the info from spreadsheet 2 going down a few lines?

    1. add a sort function to the formula and sort by submission dates or incremental data. All new rows to be added should be timestamped in a consecutive manner without any sort of backdating. This will always drop the new rows in a sorted and incremental fashion. Thats what i do to make sure the previous rows dont move while any new rows drop down under older data.

    2. Hi Jerry,

      I'm afraid there's no single option to get all of these at once.

      Q: I want to have several spreadsheets, all accessible to ONE person to edit them.
      A: You can share those spreadsheets and set the permissions - decide who can view, comment, or edit each file.
      What's more, you can protect separate sheets and ranges and make them read-only for certain collaborators.

      Q: Then, I want one spreadsheet which combines all the data from the other spreadsheets. The trick is, that the amount of rows per spreadsheet can vary. So the ranges would be dynamic.
      A: The IMPORTRANGE function will help you pull all current and future data from one file to another.
      Our tool also brings everything together but it doesn't work automatically. You'll need to run it each time you need to have combined data.

      If this is not exactly what you need, please try to describe the task in more detail.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)