Edit 29/03/2017 – Please also look at another related post – https://davefoord.wordpress.com/2017/03/29/spreadsheets-how-to-sort-data-onto-sub-sheets-based-on-values-in-a-given-column/
Edit 26/10/2016 – Please also look at a more recent addition to this post – https://davefoord.wordpress.com/2016/10/26/automatically-pull-an-entire-sheet-of-data-between-google-spreadsheets/
I have been using Google Docs for quite a few years now, and in particular Google spreadsheets.
One feature of Google spreadsheets is there is a function called ImportRange that allows you to pull data out of one sheet and into another. This can be really useful, if for example you have a spreadsheet that you are using to collaborate with others, and then somewhere along the line you want another person to be able to see some of the data in the sheet but not all of it. e.g. if you are using this to track student grades, you could have a master sheet that you and other tutors can see all of, you could then create a separate sheet for each student, and pull through only the data that refers to them (you then share that sheet with the student) and they have a live constantly updating record of what they have achieved etc.
Or you could have a mechanism where each subject tutor has their own spreadsheet to record class grades, then the course leader has another sheet which pulls all of this data into one place, so they can at any point in time see how the class is doing without having to ask tutors to email over their latest version of their standalone spreadsheet (which I see happening all to often).
Here is a video showing the basic principle of this idea.
Dave Foord's Weblog 
this is Brilliant, thank you.
Is there a way that a sheet can pull through to a 2nd sheet and that 2nd sheet is editable and feeds back to the original master sheet.
I am not sure I fully understand your question. To pull data from 1 sheet into another in the same workbook can be achieved very easily with a simple = formula.
Hey Dave Foord, can you expand on how this works. I’ve been trailing the net for a reply for the last hour and can’t find a solution! I’m specifically looking for a solution to use importrange within the same document between different tabs/sheets. I appreciate this is an old post I’m responding to!
Hi – you don’t need importrange to move data within the same workbook. Just use a simple = and then locate the tab and then cell you want to import – it should put the formula in for you.
Unfortunately that doesn’t work. I put in the = then go to the other sheet (within the same spreadsheet document), select the range of cells I want (the formula looks like this =A!A12:A150 and it produces a #VALUE with a message of “Error: An array value could not be found.”. Weird!
Looking at your formula, you are trying to pull a range of cells (array) into a single cell – hence the error.
Change what you are doing to =A!A12 then extend this formula down.
Doh! I see, I’ve just tried that and it works (of course..!). If I ever meet you in the flesh, I’ll buy you a beer 🙂 Thanks!
how to create formula on 2 workbooks where Column A in both are as product id but in work book 2 column C has product weight which should pick up automatically from wb2 on to wk1 when product id is same. I am new in excel formulas so would like some help please.
Assuming I understand what you are asking, and assuming the product ID is unique, then it sounds like you need a vlookup function – if you search for this on Internet this should set you in the right direction.
Hi Dave. I have mastered the part of getting one Google Spreadsheet linked to another one but I would like to know how to copy them without having to go and edit all the cell addresses. ie: B19, B20, B21 etc. Normally when you copy a formula it changes on its own unless you lock them with your absolute key.
Hi – if you have used the import range function, you can copy this down exactly the same way you would any function.
I get the same result in every cell….all B9 and not B9, B10, B11 etc.
I’m using the importrange and been happy with transferring/Importing
data’s , My challenges is how do i capture a report out of it? For instance
i have The Master(my report) and a sheet 1 (template use by co-worker) On
sheet 1 let say they edit it the first time it will show up on my master.
my question will be when they edit it the second time (sheet1) it shows on
my master but removes and replace the first edit. is there a way for the
first edit to stay on my Master? and second edit will go down on the next row cell?
Hi Ryan. I think what you are wanting is beyond the import range function. You could create a google form for the co-worker – when they fill this out, your master sheet gets updated with a new row each time.
hi Dave, this is something similar about my issue. i wanted to pull data from a database ODBC file to my google spreadsheet. is this possible?
I don’t know, I don’t know much about ODBC files so cannot help I am afraid. Hopefully you can find out elsewhere.
can you give me an example of your work? How to code it?
Have you watched the video showing this? This gives you the formula (importrange) that you need. There is no ‘coding’ as such.
Thanks
I think my question is the same as the first response. I would like to know if data can be automatically transferred between sheets. That would require both sheets to be editable and both will remain a copy of each other no matter which sheet is edited.
If you are pulling data between sheets, you really need to have a 1 way flow, rather than a dynamic 2 way flow, as that would be messy.
Hi Dave, I want to know how I can pull data from one sheet to another sheet in the same Google Docs. But I want to pull only certain data e.g. first sheet has many columns one of them is with status i.e. Done, In Progress and On Hold, and I want to pull only those data which are done.
asif
I have the same issue as above. I have a google form that I want to have the responses to go to separate Google Sheets which can then be used for different purposes like adding additional columns with data that is relevant to one person but not the other who is using the same data responses for their purpose on their own separate response sheet but have the original responses data be able to be edited by both users and the edits appear on both of the separate sheets, as well as future entires made on the Google Form auto update each response sheet as additional entries are made. Is it possible?
Tony – I don’t think it would be easy or a good idea to try what you are wanting with google docs – the importrange feature is great if you want to filter data and view it – but as soon as you want to start editing the data, or adding new columns/rows around the data – you are going to run into problems. On Tue 07/04/15 19:01 , Dave Foord’s Weblog comment-reply@wordpress.com sent: a:hover { color: red; } a { text-decoration: none; color: #0088cc; } a.primaryactionlink:link, a.primaryactionlink:visited { background-color: #2585B2; color: #fff; } a.primaryactionlink:hover, a.primaryactionlink:active { background-color: #11729E !important; color: #fff !important; } /* @media only screen and (max-device-width: 480px) { .post { min-width: 700px !important; } } */ WordPress.com
I am using a google doc tab named: mailing list, to track data related to mailings (name, address, date, etc) I am using importrange to transfer this information to a second sheet named:CS response within the document for the customer service staff to use. My issue the sheet CS response will have unique data added to columns and I need to make certain that this unique information stays with that entry. So far if a new address is added or the information on the main sheet (mailing list) is sorted, the additional data added on sheet 2 does not follow its original row and stays while the original info sorts. Any ideas on how I can keep the data on sheet 2 together?
Hi Asia – The importrange is limited as to what it does, and the problem you have identified being a common one, and not one that I can offer an easy solution for. The Importrange is great for transferring data across – so that someone else can view it, but if you start editing this data or adding data alongside it – you run into problems as there is no backward transfer of information to the source sheet – so any editing of the source sheet will then create problems. If your data has an unique identifier in each row, you could use this as a ‘key’, and create a second sheet, where you enter this key, and then the additional information that you want. You then have a third sheet which pulls data from first, and uses a vlookup function(s) to pull the associated data from second, but this could get messy. Remember that the Importrange is only required if moving data from one workbook to another. If you are just pulling data from one sheet to another within the same workbook, you don’t need the importrange statement. On Thu 30/04/15 13:54 , Dave Foord’s Weblog comment-reply@wordpress.com sent: a:hover { color: red; } a { text-decoration: none; color: #0088cc; } a.primaryactionlink:link, a.primaryactionlink:visited { background-color: #2585B2; color: #fff; } a.primaryactionlink:hover, a.primaryactionlink:active { background-color: #11729E !important; color: #fff !important; } /* @media only screen and (max-device-width: 480px) { .post { min-width: 700px !important; } } */ WordPress.com
Need to try what ever it is that your saying would be messy.
What about excluding certain columns from the sheet you are importing from? I only want four of the columns from the original sheet to show in my second sheet.
You only need to apply the formula to the cells you want, so in your situation you would add the formula for 1 column that you want to copy across, then you would repeat the process for the other column that you want to bring across.
So I would do the formula at the top of each column that I wanted?
Yes – you would carry out the process twice, once for each column.
Thank you! A greate video!
Glad you like it.
is is possible to pull data from several worksheets using importrange where the data in the individual sheets has the same format
I am not sure I fully understand the question. You can use the import range function lots of times to pull data in from various different sheets or different parts of the same sheet. What you have to do is specify the precise ranges.
If you are asking “can I pull in all the cells that are formatted with a green background” then I think the answer would be no – unless the green background has been added using conditional formatting and you use the same rules to manipulate the data you want – but this is getting complicated.
I will elaborate with an example. List A has contact names which has 100 records (e.g.) , List B has 20.
I want to pull 100 records from List A and then 20 from List B
After updating if List A has 101 records, then the 20 records from List B should be after the 101 records from List A and so on
There are ways to do this using functions like counta (to count number of entries in first list), if, and offset functions, but it would be very messy, and possibly problematic.
Alternatively you could write some scripts to do this – but I cannot advice here how to do this, as a bit more complex.
there should be a simpler way to do this, right?
for the list ‘a’ and list ‘b’ example,
if you’re running a function from within a particular sheet, say cell d15 = sum d1:d14, if you add a row anywhere between d1 and d14 the equation will automatically update to d16 = sum d1:d15
i don’t see why this wouldn’t be possible with relative simplicity when pulling data from one sheet to another, but i seem to be unable to figure out how to do it…
Spreadsheets (whether google, excel or other) work best when you have a known and fixed range that you are working with. The problem that you are discovering is you don’t have this fixed range, which is always going to cause problems I am afraid.
when you pull data from a master spreadsheet onto a new spreadsheet, is there a way to also have the text style be imported too? for example if i use the strikeout text for a whole row and that row is imported into a new spreadsheet, i want that strikeout to show up.
I have the same question. any tips anyone?
One way to do it is use Copy to – copy the selected sheet to the destination work book. The use the importrange function on the same copied sheet. The result is the new imported sheet is formatted as per your original sheet
Brilliant idea, Mr. Foord–and now it seems so obvious.
Hi Erik. I don’t know if this is possible or not, and if it is I don’t know how. The importrange function is designed to pull through the data not the formatting. Sorry I cannot be more help.
awesome! a clever little solution for sure. thanks.
Excellent presentation of this topic! Thank you very much!
Obviously, though it has been a while, this post got people’s attention. What I am hoping to do is to use all of the tutoring session attendance forms submitted by our district’s teachers to collect all of the data in one large report that tracks student participation by school. However, I am a Google Docs spreadsheet neophyte. I don’t mind figuring it out, but if it is something that cannot be done, please tell me! Thanks!
Hi Trevor. This definitely can be done, and the importrange function is just what you need.
For you to be able to analyse the information easily, you would need each schools sheet to be set up in a set format so the structure is the same for each school, but even if not the same, you could use different formulas within the spreadsheet to manipulate the data into an analysisable format.
If you are familiar with excel, then you can use most of the same formulas/functions in google spreadsheets.
Excellent. THANKS. The forms they will use will all be provided by me and are identical, so that is no problem.
Thank you, this was a brilliant tutorial and just what I needed! 🙂
This tutorial seems to have been very useful to a lot of people. I am glad I have been of assistance.
Hi Dave, Thank you for the great video. I was able to pull data from multiple spreadsheets into one master, but could only make it work if I gave each master spreadsheet it’s own sheet within the new master. How would I alter the importrange function to put all of this data in the same sheet? Each of the original spreadsheets is in the same format as the new master.
Thank you!
Sorry, I should say “but could only make it work if I gave each of the original spreadsheets it’s own sheet within the new master.”
Hi Kris. Having pulled the data into your new master document, what you should be able to do is create a single new sheet in that, where you use a simple = statement and pull the data from the many sheets into this one. You can then hide the many sheets – so they are not visible and just have the single sheet showing.
How would I combine multiple sheets from that specific document? For example, =importrange(“key”, “Sheet1!C:C AND Sheet2!C:C”) ???
Say I have two sheets that has a roster of employees in Location 1 and Location 2, I’d like to have a full roster -or- perhaps a means to grab their data to make a custom csv file. For example, only grab their first, last name, and email to bring into a newsletter. All other data is irrelevant since we are using =importrange – How would I import multiple sheets?
p.s. what commenting plugin is this? :3
I have no idea – it is just whatever comes as part of a WordPress.com blog.
The way that I would go about this, is in my destination file I would create a sheet for each of my source files and pull the data for each sheet in using importrange function. Then I would create another master sheet in this document and just use a straight forward = function to pull the required data from the seperate sheets into this one. Once the mechanism is set up you can then hide the sheets containing the importrange if you wish.
Mr. Foord,
Great tutorial video and I am almost where I need to be but I am losing the cell format of the cells being imported (bold, color fill, etc.). I am trying to track multiple project status sheets on a single page and I am using color coded cells per sheet to show the status of the project.
Is there a way to import over that data so I get a color coded dashboard for all the sheets. Either way thank you for this information and for taking your time to help others.
Rex
Hi Rex. You cannot import the formatting of the cells only the data, so if using colour as a means on information, I am afraid there is no way to bring this across (as far as I am aware).
You may want to explore the conditional formatting – which could be used in both sheets – for example if you want to highlight a date that has passed, you can use conditional formatting to highlight this (e.g. Colour it red) – which would work, but anything that is manually formatted we cannot do anything with.
Thanks Dave the conditional formatting worked perfect and has given me the effect I was looking for.
Thanks Again,
Rex
Hi Rex. Glad you have found this useful, and you have discovered a solution that works.
Hi Dave. This was very helpful. I have another issue that I hope you can help with. It seems simple but I cannot find a fix on my own. I will have a list of 50 – 60 names of associates working on different aspects of a given project. That list of names will reside on its own sheet (sheet 2). On sheet one I would like to have a drop down list (like a filter) of those names in order to assign them to various tasks within the spreadsheet. Does that make sense? Is there an easy way to do this? Thanks for your help!
Hi. This is very easy to do. On Sheet 1, select the cells that you want to contain the dropdown boxes, then go to the data menu, and select data validation. Next to criteria you select ‘Items from a list’. You can then enter the range which contains the names of the people that you want to appear. If your list of names is in column A, then you will need something like Sheet2!A2:A100 – but enter whatever range your names are in.
That works perfectly! Thank you so much. This is a huge help.
Coop
Hi. I am trying to use this to share with different people diffrent parts of a spreadsheet. But, I’ve found a problem. I can’t hide the importRange formula to them(I’m sharing it readOnly and with the cell that has the formula hidden) so anyone can copy the page and edit the formula to expand it and see the complete page.
Any ideas on how to solve this?
Thanks!
Hi. You have identified a very interesting and significant problem, which is rather annoying, and I cannot think of an easy solution.
I would have hoped that the ability to protect cells within a sheet would come to my rescue, but even doing this doesn’t work when the person takes a copy of the sheet – they can then unprotect the copy and see the formula – and once they have the sheet key they can create their own importrange to pull the data that they want into any sheet.
I wonder if any other followers of this thread can help here?
Hi Dave, I found a workaround for this problem, but it has other issues.
This is what I did:
– I created a spreadsheet with the data.
– then I created another spreadsheet that took the data from the first using importRange
– Then I created a third spreadsheet that took the data from the second one using importRange
– The third is the one I shared
The only problem with this, is that it is very slow, It may take more than 5 minutes to pull the data or refresh
Hi. I had considered this, and agree that it would have a time delay, although there can be a 5 min time delay anyway.
When I tried this method i then managed to find a way to ‘hack’ my way back to the data in the original sheet, which is why I didn’t suggest it.
I will be doing some more investigative work with google spreadsheets in coming weeks, and I am hoping I can find a better way to properly protect cells within the spreadsheet.
Hi Dave.
Have you found a solution to this problem in the last months?
Thanks!
Hi. I haven’t found a clean solution to this problem, but I haven’t been looking for one, as I don’t need this at the moment for my work.
I did work out that if I introduced an intermediate file, then I could better protect information, e.g rather than pulling data from FileA to FileB, if I pulled data from FileA to FileC and then pulled it from FileC to FileB I could create improved protection options but it wasn’t simple and seemed like the wrong way around it.
I am doing a lot of work with microsofts skydrive at the moment and I have greater control in that to manage things within Excel etc. so that may be the way forward.
Sorry I cannot be more help.
This is just what I needed, Thanks so much. However, I have a few questions. First, my spreadsheet key does not have an & at the end, so I am not figuring out what exactly I need copied.
Second, and this might have been answered. But I will be having many worksheets, one for each day, and I want to pull information from say 4/12/2013 worksheet and have a function to put it in the right cell in my other summary worksheet, which displays daily info, then for the 4/13/2013 worksheet, display the total to the 4/13/2013 date in the summary worksheet. Is that possible or is that something I would have to function everyday? Thanks for your help!
Hi Rob. If you are having difficulty getting the importrange to work, the best thing to do first would be check googles website on this http://support.google.com/drive/bin/answer.py?hl=en&answer=155183 to see if that explains the process better.
In terms of your second part of the question. If each day has a different workbook for it, then you would need a separate importrange function for each day, which would be tedious. If each day has a separate sheet within the same workbook – if the data is always in the same place on the sheets, then you could use functions to pull that data into a summary sheet within that workbook, then use the importrange to move the data into a new workbook – where you again use functions (vlookup would be the most likely) to sort the data into the correct places.
Hi Dave, I’ve been using the import range function for some time but have not figured out how to tie the source cell to the importing cell for cases when the source cell’s column or row is moved.
If I’m using a simple =function and I wanted the two cells to be connected regardless of position I’ve found I need to insert $’s where appropriat. Ex: =$A$1. If the position of A1 shifts over to column B, the import cell will contain the content from the new position, B1.
With import range, if source cell A1 is shifted to column B the import cell will still reference the origanal A1 position.
Is there any way around this? I’ve tried including $’s with import range. Not sure if that works with this funtion and not sure if I’m putting them in the right places. Please advise.
thanks
Hi. Using the $ signs to make an address absolute rather than relative does work for normal functions, but won’t work with importrange. This function does rely on the structure of the sheets not changing. I cannot think of any way round this.
Hi Dave,
How do i automatically create a row in one sheet based on a new entry in another sheet? (both sheets in the same spreadsheet)
Thanks
Sharad
Hi. I am not sure I understand the question. If the sheets are in the same workbook you could use a simple = function to pull data from one sheet to another. The downside of this is blank cells will pull through a 0 rather than a blank. There are various ways of overcoming this – you could use conditional formatting to format the 0 the same colour as background so you don’t see it, but you then wouldn’t see a 0 that should be there. My preferred method is to use an if statement so something like:
=if(sheet1!A1=””,””,sheet1!A1)
This checks to see if A1 is blank, if it is it returns a blank, if not it returns the contents of A1
Hi. I am not sure I fully understand what you are trying to achieve. Can you give further information so that I can try to answer correctly.
Hi Dave, is it possible to combine data from 2 google spreadsheets into 1 in the following case. sheet 1 has columns containing ID, Name, age, address. sheet 2 has columns containing ID, height, weight, comments. i would like to know if there is a way combine a row from 1 sheet with the a corresponding row from a 2nd sheet that have the same ID.
so, in 1 row i would get: ID, name, age, address, height, weight, comments.
i hope my example is clear enough and i hope there is a way to do that.
would really appreciate the help.
thanks,
The way that I would do this is use 2 importrange functions to pull all the data from each source into 2 separate sheets in your destination workbook.
You then use a vlookup statement that looks up the ID value on one of the sheets and pulls the additional required fields of data from table on other sheet.
I hope this makes sense.
Thanks for your help. I think I got it to work 🙂
Hi Dave,
I must echo everyone’s comments… This is the best explanation I could find re: the Import Range function and it worked perfectly. Thank you!
Also, as most above, I have yet another question. Here’s the run down…
I have one Master List (Sheet 1) that auto-populates other sheets via the Import Range function. The reason I’ve created the different sheets is so that I can sort the data from the Master List/Sheet 1 in different ways.
When I attempt to sort any additional page (sheets 2-6) differently than Sheet 1, by going to Data -> Sort Sheet by Column A, A-Z, it just simply will not sort. (bear in mind, there is no issue sorting on sheet 1, so it seems to be the import range application that is preventing these subsequent pages from sorting correctly). Not only will the sheet not sort alphabetically, it also blanks out certain cells and replaces it with the contents of that cell with “–“.
I’ve looked up ways to apply to “Sort” code to different sheets but that also doesn’t seem to work with an import range has been applied.
Hopefully this makes sense and I would love to hear your thoughts as to how to fix this issue/glitch. Thanks so much!
Glad you like my post. Yes sorting a sheet that has got an importrange in it is going to be difficult as the sort won’t work on any area containing formulas, which has always been the case with spreadsheets.
The options are:
Before doing the sort you could manually copy the data you want to sort, then do a paste special and paste values only into a new sheet, then sort it.
Or I have created formulas to do the sorting into a new sheet, by using. The rank function to find an items ordered position within the list, then using the offset function to put the item into that correct position on another sheet – this does work but is a little complex to set up, and once the list is created you cannot add any further information in to it, because as soon as you add new rows into your master sheet the subsequent sheets would all change order.
Hey Dave! I am new to Google Docs and am having to use it ALOT for my new job. I frequently use your tutorial and I thank you for making it!! I have actually used the Import Range function so much that I am getting an “Oops” message stating “At this time, spreadsheets in Google Docs only support up to 50 Import Range functions in a single document.” I have one main sheet that I use to feed to multiple sheets. Any ideas other than having to create another sheet to pull the information from?
Glad you like my tutorials, and wow that you have got to 50 import ranges, I have never used more than a handful.
I don’t know what to suggest I am afraid.
If you Google this, you will find ways around it by cascading your sheets. It is a pain, but it is certainly possible.
Just what I wanted. Thank you so much Dave.
Hi Dave,
Great Video but I’m not sure if what I’m trying to do is possible and was wondering if you could help me out. So I have a google form that has a drop down with 13 names on it that the responses go into a spreadsheet. I want to take the data and create a sheet for each person. so when “joe” enters his info it is pulled from the main sheet into a “joe” only sheet. since each persons name is entered into the same column on the man sheet i’m not sure if i can have the importrange look for a specific name in a column to pull into a new sheet. thanks again. Brad
Hi Brad. I don’t think importrange is designed to do what you want. You could use the importrange function to pull all data into a hidden sheet in Joe workbook, and then use a combination of if statements, vlookups and a few other functions to pull only Joes data into a visible sheet, but the other data would be there and potentially visible to anyone with access.
The best way to do this would be to use scripts to do what you want (basically coding a bit like macros in excel). And this would definitely do what you want. I am not an expert on scripting so cannot tell you how you will need to search elsewhere.
Thanks for the reply.
Hi Dave, this is really useful. Thanks for putting it up here. The project I’m doing is aiming to do a timeline of political events. I want to set up a number of different spreadsheets (Economics, Education etc) and then combine the data into one master timeline. I read in the comments about importing data from various spreadsheets into different sheets of your master spreadsheet and then using = to combine them. My question is what if the data keeps increasing. For example, I keep adding new political events on different sheets how can I make my master sheet smart enough to keep adding that data in? Can you write a formula which is “Take all data from this sheet”?
Bit off-topic of importrange but I thought you might be able to help. Thank you again Dave.
Yes what you want is possible, and there are various ways to do this. I am assuming that you want final list to show events in chronological order, if yes then pull all events into their own sheet, and then pull all values from these sheets into a single column in a single sheet (leaving gaps to allow for additions). You can then use the rank function to find each items rank (order) within the list – you then use this number on another sheet in an offset function to display the list in the correct order. As new items are added the rank values change automatically and the list reorganises itself accordingly. Sounds complicated, but is actually quite simple if you are confident with how functions work.
Hi Dave, thanks for useful video!
I have the same question as above. Could you write a practical example of how to do it? I am new to this.
Please help.
Hi Sovann. The video that I produced is as good an example as I can give. If you have used spreadsheets before (e.g. Excel) then in Excel you can use a simple = statement to pull data from one workbook to another. In Google Docs all the importrange is doing is exactly the same thing.
Hi I had some trouble with importrange:
How can I import multiple cells from one spreadsheet in to a single cell of another spreadsheet (in other words the single cell should calculate the total of the mutiple cells?
Example: In doc named “doc1” cell B4 and F4 each have the number 50. I would like “doc 2” cell F3 be linked to those cells and automatically say 100. and if i change those cells to 40 then “doc2” cell F3 will say 80.
I hope I’m clear.
Thank you
What you need to do is set up 2 separate importrange statements to pull the 2 values into 2 different cells within your workbook (possibly into 2 different sheets), once there you can simply use a function to add the values of the 2 cells together.
Hello,
The tutorial was really helpful.
I have a question though and would appreciate if you can help me.
I have basically created a web assessment google form and I am trying to use the assessment responses,captured in a google spreadsheet, to feed data to a microsoft excel 2007 which is then creating bar graphs based on the response.
Is there a method to use the ImportRange function in such a way that only the last row that is updated in the first spreadsheet gets copied into the second spreadsheet. My intention is to use the ImportRange function in the second spreadsheet in such a way that it extracts only the last updated row from the first spreadsheet.
Any help here would be really appreciated.
I am not sure I fully understand what you want, but if you set up an importrange this will pull all of the data into a new sheet with the latest entry being the bottom row of data. There are various ways to then pull this last row into a different sheet. You could use a max function to find max date in the date stamp column, then use a rank function to find its position within list (basically how many rows down the table it is), and then on offset function which offsets by this number will give you the data you want.
Hello,
Thanks !!! I was able to figure this out but have been stuck with another issue now.
Is there a way to automatically push an entire row of data from the google spreadsheet to a sheet in microsoft excel 2007?
My aim is, as soon as a user submits an assessment response, it automatically gets pulled from the spreadsheet to the excel which then creates a graph for it.
I am not aware of being able to automatically move data from google to excel.
Hi Dave,
Great info! I tried to find the answer to this, but I can’t seem to get the right formula to make it work.
Here is my situation: I have a sheet in a Google spreadsheet of phone/email/contact with each student in my school (example of column headings: Student Name, Date, Type of Contact, Concern). All teachers in the school can access this document to add information in about students. I then have additional sheets in the same spreadsheet that are sorted by student name (Example Sally Smith, John Doe). I would like to have all contact information from every teacher about Sally Smith auto filled into her sheet so that I can see who contacted that student and when, and all of John Doe’s info auto filled into his sheet, etc. How do I do this with ImportRange? Or am I on the wrong track?
Thanks so much!
Hi, If the additional sheets are in the same file then you don’t need importrange, as this is used to pull data from 1 book to another.
What you want to achieve is possible with a combination of basic formulas and function but more than what I can explain here.
If you have a budget and you are able to give me access to a copy of the workbook I could create this for you, quite cheaply (I would need to see the sheet before quoting exact amount).
Thank you for the offer. As with any public education entity, there is no budget. I will continue to cut and paste, and search for the formulas/functions.
very helpful, thank you !
Glad you like it.
THANK you … wonderful and very openly generous of you to share!!!
Hi Dave, first of all, sorry for my english, I´m not an proper english speaker, but I´m gonna try to explain my question. Thanks for the tutorial , it was really helpfull, i´m working with a really big google docs project with forms and several spreasheets. I want yo ask you if it´s possible to see the content you are importing in the destination cell. I only can see the formula, but I´m sharing the documents with other pleople and I need them to acces the content too. Thanks!!
Hi. No problem with your English – made sense to me.
When you add in the importrange function you will see initially the formula that you are adding, but as soon as you leave this cell and click on another one, the data should pull through and you should see that rather than the formula.
Thanks Dave, I din´t explain my problem well, I was trying to ask if it was possible to edit the content in the destination cell, cause´when I click there I just can see only the formula although when I move to another I can see the data as you say. Anyways, I found that I can just select the cell and copy paste the content, but maybe you know some way to make the import data editable in the destination cell. Thanks again!
Hi – correct. If you want to take the data out of the importrange area then you are looking at doing a copy and paste, as anything else would effect the formula that pulls the data in.
Hi Dave,
I am using importrange to copy values from my master spreadsheet. The master sheet is constantly changing. The challenge I am having is that the data in the imported range does not always stay with the data that is manually entered. For example: Columns A, B, C imported, columns D, E, F manually entered to line up with the corresponding row. When the master changes A, B, and C, they no longer lined up with D, E, and F. Is there a way to “lock” the row together? Thank you!
Hi – not an easy question to answer, and no it isn’t possible (as far as I can work out) to ‘lock’ the rows together. If there is a unique identifier in the data (e.g. a membership number or similar) then you could set up the data so that you enter the matching data in a separate table, and you then use a vlookup function to bring both sets of data together to view them. With this any changes to the source would sort themselves out with this view sheet.
Hello,
The Importrange function is very usefull, I have only one problem with it.
When I like to import a time from another doc, the time isn’t showing the same way.
The source file shows “9:30” and on the Importrange file it shows “31-12-1899 2:02:32”.
I’ve tried all the formats for the cell but it isn’t showing it the right way.
Do you have any solution to fix this?
Kind regards,
Evert
I don’t have a solution, I haven’t come across this before, but can only assume the date/time settings of the 2 sheets are different and it is getting confused.
Dave,
This page has been immensely helpful, so thank you. I have a question that I feel should have a simple solution, but I’m a little stumped. I want to have a spreadsheet that is populated from a form to be cleared at the end of each day, but the results to be sent to a master sheet first. For example, all the results we get on Monday would be visible on sheet 1 until the end of the day, at which point the data would be pushed/pulled to sheet 2, and sheet 1 would be cleared. Then on Tuesday, the process would repeat, except the data from sheet 1 would be added to the data on sheet 2 rather than override it. I have created a function to run around midnight that wipes the first sheet, but it also deletes the data from the 2nd sheet.
Do you have any ideas/thoughts on doing this, or some sort of alternative?
Thanks so much for this page!
Jeff
You could use functions or another alternative would be to have the form populate the spreadsheet as normal, you could then use an if statement to check if the date was the same as today, if yes pull the data into an interim sheet. You then have another sheet which uses something like an offset function to pull the data from this interim sheet into the top rows of a third sheet. This then becomes your sheet for that day
Hi Dave,
Great post and thank you for your contribution …
Slightly different issue, two questions please:
1. say I have a spreadsheet S1 linked to a Form F1. How can I pull data from S1 into the form F1 based on a user entered value – say they enter their name, and then doing Vlookup I can ‘fetch’ their contact details and automatically populate the relevant contact details fields in the form – i.e. how can I do Vlookup formulas inside the form ?
2. is there any easy way to automatically create a Form from a spreadsheet, and at the same time automatically create all the fields with names as per datasheet column names ? (as far as I know we have to manually create in the form as many columns as we have in the spreadsheet – very tedious work and seems silly to me that this has not been automated already … may be I’m missing something ?
many thanks
For your first question you are certainly going beyond the purpose and remit of Google Docs – you need a proper database driven website for that.
For the second question, there is (I think) the function to create a form from a spreadsheet, but I found this slower than creating the other way round e.g. Create the form that you want, and it then generates the resultant spreadsheet.
Hi Dave
My issue: I am importing data from sheet 2 to sheet 1 using importrange(). On sheet 1, when there are already few rows below the point where i give this function, how to make those rows below move down after inserting the range of rows from sheet 2?
Thanks in advance!
-kevin
Hi – what you are wanting is not as easy as you would imagine, as it won’t automatically move the lower rows down when new data is added.
You could look at writing a script (code) to pull data from 1 sheet to another, and then move data around.
or you stick with the importrange to bring the data in, but pull it into a holding sheet, you them get clever with complex formulas to pull this data across into your main sheet, unless a row is blank, in which case pull across your other data from other source.
However this second technique would only work if viewing the data, you wouldn’t be able to edit it in this final sheet.
Dave Foord
>
Hi, thanks for a great site!
My question is if it is possible to import a comment from one field in a separate google sheet document, to another spearate google sheet document. I have already set up the ImportRange function and also connected data validation but the comments dont seem to get transfered. Thanks.
That is a very good question and I am afraid I do not know a solution. Comments don’t tend to transfer from one sheet to another as have tried in the past.
Sorry I cannot be more help.
Dave Foord
>
How do I pull the data to another sheet based on a key word used in a validation function in one of the rows?
I have a source sheet and 3 additional sheets (4 sheets total). The sources sheet has columns with validation function in several of the columns. In one of the columns (column J), reflects the name of the 3 other sheets. I want to be able to pull the entire range of data from the row based on the validation chosen in column J to be exported into the appropriate sheet. Then I’d like to a 5th sheet with a pivot table providing the totals of each of those sheets. Does that make sense??
Hi Hudson. I think what you want is more complex than what I can help you with here. Your options are to pull all the data through using importrange and then use a combination (of quite complex) formulas and functions to manipulate the data – or you need to go down the writing a script route, which may be best way to go here, but not something that I can help you with, as my scripting skills are very limited at the best.
Dave
Hello every one
I am working in importrange function from last 2 years. I want to know that I can import values from 1 spreadsheet to 2nd but cant get same formatting from 2nd spreadsheet. for example If A1 has a cell value 2000$ with Red Background color but in 2nd spreadsheet it comes only 200$ without background color.
Is it possible to import data with background color (formatting).
thanks
Hi Dave,
I have created a Google Spreadsheet. It contains multiple sheets (i.e. Mr. A, Mr. B, Mr. C, Mr. D and Mr. E) where people are listing down their tasks along with the status of task (i.e. Done or On Hold or In Progress). I want to pull only those tasks which are Done at one sheet from all the sheets.
How is this possible?
Please help
Asif
Hi Dave, this was extremely helpful- thank you! I have linked two spreadsheets and am editing data on the original only. i.e. a one-way flow. But on the copied sheet, I would like to delete certain lines and still have it connected to the original (so I can share it with someone and they will only see the lines relevant to them). I tried deleting a line on the copied sheet, but Google automatically puts it back in after a couple of seconds. Any advice is appreciated! Thank you!
Hi Melissa, if you want to edit the data in anyway, you will need to use some clever formulas and functions to pull the data you want into a different sheet within the destination file.
Hi Dave,
Great Video. Just wondering is there a way to sort the data so that it can split the data into separate worksheets.
I have a google form collecting store number, employee name. I want to import the data but give each store their own tab (which automatically updates the responses).
For example with your spreadsheet, importing the data and splitting the countries into separate sheets so they all have their own tab.
Yes this is possible if you know the definitive names of all the countries (and they are always spelt the same way in the starting sheet) but would require some clever use of formula and functions to do this.
Or another option would be to write some script which could be set so that as soon as data is entered in one sheet, the script would copy that row of data into the relevant sheet for that country, and if necessary create a new sheet if a sheet for that country doesn’t exist. This would be my suggested route as once created less potential for things to go wrong, but it depends on whether you can find someone to write that script for you. This was one of the ideas used in a project that I worked on – details of which can be found at https://davefoord.wordpress.com/2012/05/25/using-google-apps-to-create-a-fast-feedback-tracking-system/
Hi Dave, that was a great video. At first I thought it was exactly what I needed but once I started I realize I need something a little different. Do you know how to do the following? I am using one google spreadsheet, in it I have 7 sheets; 6 of them we will be adding data too and the 7th we want to use to reflect all the data that we are putting into the 6 sheets, almost using the 7th as a snapshot of the others. All of the sheets have the exact same columns but just are broken into the different projects they are for. Is there a way to do this? Any information would be greatly appreciated! Thanks.
Jennifer,
I’ve been doing this very thing with vmerge. Just look for and install the vmerge script in the script gallery. I believe it has instructions with it, but if not, just google ‘using vmerge to combine data google docs’ or something similar.
Good luck!
Hi Jennifer – you are correct that you don’t need the importrange for what you want to achieve. Jeff has also replied to you mentioning vmerge script – which I haven’t used or come across so cannot vouch for this but sounds promosing. If you wanted to do it the old fashioned way, you could use some basic formulas – but to do this you would need to estimate the maximum number of rows that are going to be filled up in each of the 6 sheets. You then use a simple = statement to copy all cells (and the blank cells you have just estimated) from each sheet into a temporary sheet – you will initially get lots of rows containg lots of 0 which represent the blank rows. You can then use a if statement to number the rows 1,2,3, etc but if the row is one of the blank ones you give it the same number as the one above (so numbering may go 1,2,3,3,3,3,3,4,5,6,6,6,6) etc.
Then on another sheet you use a vlookup statement to look up the row number against the table you have created on the temporary sheet, and pull through the data for that row against that number. This will effectively remove the blank columns. As soon as any new data is added to any of the sheets – that data will appear in the correct position and all other data below it will move down one.
This may sound complicated but is actually a lot simpler than I have probably described it. The only problems is that you do have to specifiy a maximum number of rows in each sheet.
You can use vmerge a google script
Thanks for the input. I am still struggling on getting it to work but could be because it’s been a while since I have worked with spreadsheets. I will keep searching! Thanks for the tips.
Thank you very much, I needed this feature!
Great video thank you! I wonder if you can help me. I have a formula on my first sheet which feeds my client names into the other sheets of the spreadsheet (each sheet holds different information about each client). The problem is, when I insert a new row (a new client) into the first page, only the client name is inserted into the other sheets and not an entire new row so the new client’s name appears next to a previous client’s name. Any help would be much appreciated. Thanks
What you have described is a common problem with using spreadsheets this way – if you add rows into the middle of one sheet the formula on other doesn’t then work.
You could use a script (basically a small bit of programming a bit like a macro in Excel) to add a row when you need one, and get the script to adjust the other sheets at the same time so this doesn’t happen, but that requires someone with the ability to script that.
Or you always add rows at the bottom of the sheet, and don’t insert them – then use formula on the other sheet to order them somehow and a vlookup statement to pull through the rest of the data from whichever data you have sorted, but again this requires someone with the skills in the spreadhseet to do this.
I have a master sheet that feeds other sheets using a filter. When old data is deleted from the master sheet, is it possible to keep the data from being deleted on the destination sheet?
Hi Bonnie, for this you would need a script to pull data from one sheet to another, otherwise deleting data on master is going to impact on the destination sheet
I’m pretty new to all of this! Any suggestions where to look for a script that would work? I’ve googled everything I can think of and haven’t found a solution.
https://developers.google.com/apps-script/articles would be a good starting point. What you want to do is relatively easy, so if you work through tutorials you should get there.
Dave, I really appreciate all the help you’ve given, but I’m missing something in all the responses – I have just one cell i want to reference from the master workbook to my current one. it’s not a range of cells, just one cell.
Thanks
Hi Denis, you use importrange as per the video, but don’t copy the formula down – just have it in one cell of the destination sheet. I think!
I am not sure if this question has been asked before, if so I apologize. Here is the scenario: spreadsheet responses that collects responses from the form. I need to leave this intact – ie. users should not be editing this spreadsheet. So I would need to pull the data from it into another spreadsheet. I know I could do this easily with importrange. However, the problem is that users cannot edit it. Basically I would need to pull data from the original spreadsheet to another spreadsheet, so that users can edit it – but this editable sheet should also be able to pull additional data from the original spreadsheet when it gets updated with new responses. I suppose this is not possible? Many thanks.
Hi Alice.
It is possible to do what you want, but you would need to make use of a script. This could be set up to automatically copy new data from one sheet into another. Once this has been copied and pasted you could then edit the data as much as you like in the destination sheet.
I am afraid I cannot help you with the creation of such a script, as that is not my skill area.
Dave > > >
Wondering if you could help. I use a Google sheet to track all student grades for the year. I have all the students listed alphabetically in the A column, modules along the top of the sheet and corresponding grades going horizontally across the screen next to each student name. I want to set up individual trackers for each student to share with them so they can monitor their own progress without seeing each others grades. I have used the import function from your video (which is great by the way), but as each student is in a different row I have to alter the range manually in each individual tracker. Is there anyway to import a student name rather than a range so if I were to type in Joe Bloggs it would import his grades but then if I typed in Jane Doe it would import her grades? Hope this makes sense.
Thanks,
Nathan
Please refer to this link for Query Example spreadsheet
https://docs.google.com/a/inventa.in/spreadsheet/ccc?key=0Am5Xsf1r3lGjdENCQzJEX0NLOGhvWnpGbUFvTDA1YVE&usp=drive_web#gid=0
This should answer your question
Dave,
I did read your previous posts on conditional formatting and wanted to make sure I understood the answer. I have been using the … (=IMPORTRANGE(“1H……………………….88″,”Sheet1!A1:GF1”) formula to pull data from one sheet to another. On my original sheet I have some important conditional formatting in some of the cells.
Is it possible within the (=IMPORTRANGE(“1H……………………….88″,”Sheet1!A1:GF1”) formula to allow for the conditional formatting to carryover. I utilize certain colors to emphasize data and currently it cannot be seen in the sheet that has the copied cells.
Thanks,
Brien
Hi Brian, I don’t think you can get the importrange to pull formatting through – only data. You could apply the same conditional formatting rules to the destination sheet, so the cells would change colour based on the values.
Dave Foord
>
That is what I feared. Thanks for your insight.
Hi Dave,
thanks for the video. It’s been very useful.
I have a problem with importrange when the source range has some random values (generated with randbetween, for example)
It seems to get a new seed to get the import, so I see different values in the source than the destination. When I force a change in the destination, no change occurs (as expected). When I force a change in the source, a change occurs in the destination, but again different from the source.
I’m a bit upset with this. Any hint to get the same values? Is it an issue with importrange? Thanks.
Hi Juan, random numbers in spreadsheets are always a bit awkward, as they regenerate themselves every time something in the sheet changes. I cannot add any further insight I am afraid.
I’ve just taken a workaround with the randomness in my spreadsheet so I just generate random numbers in a sheet, copy them as values, and use this static copy to do the importrange. For me is enough. I just want to leave this comment in this entry blog, which I think is the best about these topic. Thanks again
That is the way that I usually use random numbers in spreadsheets, so I wouldn’t call it a workaround.
Hi Dave, perhaps a tad off topic, but I have been charged with the task of linking a form to a master call base. I want to be able to enter a telephone number and for this to auto populate the Name/Address fields if matched in the master. Is this possible??
Many thanks
Neil
Hi Neil, very easy – you need the vlookup function – this looks up the phone number in a table of data, if there is a match it returns data from a defined number if cells to the right.
Hi Dave,
Thanks for your reply. I’m not sure I scoped my problem particularly well. Can vlookup or index/match be used within the form? ie I enter a telephone number into the form, this then looks up my data table and if found, populates the form for a selection of fields?
Hope this is a little clearer.
Neil
Hi Neil – I understand your request now. I don’t think what you want is possible with a google form (but I may be wrong) – but could be achieved quite easily by a web developer (which I am not)
Dave Hows it going, I have a question, I have a form that tracks work performed by technicians in which each tech arrives on site and selects there name as one of the questions among others. Now since I was not able to find a function to copy the data entered by the tech into an individual TAB on the same sheet with there name, I have had to duplicate the form multiple times for each tech (around 10) Name the form after the tech, and change the “Response Destination” to “send to new sheet in existing spreadsheet” and rename the tabs according to each person. Now this has worked flawlessly for the exception of one thing,… my form is constantly evolving based on our needs and having to change each form individually (which I have not even attempted) will become a grueling process. “”My question is”” IS THERE A WAY TO COPY A ROW OF RESPONSES TO ANOTHER TAB BASED ON THE TECHNICIAN COMPLETING THE FORM. AS TO SAY IF RYAN IS SELECTED AS TECH, CAN I CREATE A TAB NAMED RYAN AND COPY ANY ROWS CONTAINING THE WORD RYAN IN THAT SPECIFIC CELL TO ANOTHER SHEET IN THE EXISTING OR EVEN A DIFFERENT SPREADSHEET?? Any feedback will be greatly appreciated. Thank you
Hi. What you are wanting is possible – but requires the writing of something called scripts. The mechanism you described is exactly what we did with a project at Loughborough College – a link is https://davefoord.wordpress.com/2012/05/25/using-google-apps-to-create-a-fast-feedback-tracking-system/
You will then need to follow the link further down to Martins blog.
>
Hi, Dave Foord, please help me. I need to ImportRange those fresh entry when Form Submit, BUT the entries stay on the intended spreadsheet did not copy UNLESS I did some activities (e.g. adjust the width of the column, bold the font and then undo, Hide a column and then unhide it back). Once I did x activity, then the spreadsheet will automatically fetched the data from the master spreadsheet. Why can’t it populate the spreadsheet automatically from the Master spreadsheet? Please help.
Hi Max – it does do it automatically, but there is a time delay of up to about 10mins
Hi Dave,
I know you have said that it is not possible to carry over formatting with an Import range but do you think you could take me through what I would have to do get the conditional formatting to match sheet 1 with sheet 2 (the imported range cells)?!….
Thanks
Hi. Firstly you need to apply the conditional formatting to sheet 1 – if you don’t know how to do this look at:
https://support.google.com/docs/answer/78413?hl=en
You then need to copy this conditional formatting to the destination sheet. If Sheet 2 is in the same workbook as sheet 1, then you can simply use the format painter icon (looks like a paint roller) to copy the formatting. If Sheet 2 is in a different book (which if using ImportRange will be the case) then sadly you cannot do this directly, so the solution would be to copy sheet 2 into the same book as sheet 1. Then use format painter to copy formatting from sheet 1 to sheet 2, then copy the sheet out of book 1 and back into book 2. You may need to check that the Importrange function is still working at this point.
Thank you! How would one adjust the formula to add a 2nd or 3rd set of ranges within the same tab? For example, if one wanted Leads!A1:O9999 AND Leads!AB9999:AD9999 only but skipping columns “Q-AA”?
Again…great info here…
how to you stop editing on the second spreadsheet? So it just shows the data pulled from the 1st- at the moment when i edit anything on the second spreadsheet it comes up with an error but i don’t want it to do anything at all.
The importrange function literally mirrors what you have in the first spreadsheet. By doing anything to the data you see in the 2nd spreadsheet, it interrupts the feed and causes an error. If you need to edit the data you must either do that in the first spreadsheet (so it shows in the second also), or copy the whole of the 2nd spreadsheet data and do a ‘paste values’ under ‘paste special’ but then you lose the link to the 1st spreadsheet and the data is no longer a ‘live’ copy to the 2nd sheet.
If you don’t want the second spreadsheet edited at all, then you should protect the sheet by right-clicking on the tab and selecting an appropriate protection.
Hello. Amazingly helpful, II wonder if you could help me..I am creating a spreadsheet to manage job enquiries. The spreadsheet is linked to personal spreadsheets for my employees. I enter the date of the new enquiry at the bottom of the sheet in column A, then in column B I ask one of my people if she would like to work on the job, this shows on her doc. in column C on her doc she then types her reply, which shows up on my doc in column C. This works really well so far, but I need to be able to keep the spreadsheet in date order. If I filter by date in column A, column C gets scrambled, so that her responses are not shown next to the right enquiry. Is there a way around it? I have tried inserting the enquiry into a new row to keep the dates in order, rather than using the sort function, but the new row doesnt feed through to the employees spreadsheet. Any advice would be hugely appreciated.
Hi Jenny. Importrange is literally a mirror of the data you are importing. You can’t sort the mirrored image, you can only sort the original image, which would then be reflected in the imported data. Therefore, if you have column A as an imported range, then add ‘raw’ data to column B, you won’t be able to sort column A and expect column B to sort too. If you sort by column B, it will sort it, but column A will stay the same (because it is mirroring the data in the original data source). If you sort the original data source of the imported data, it will sort, but column B’s data will stay static (hope you followed all that!). Adding a row in the original data source would only add a row in column A, and column B would also need a row added to compensate.
Therefore, importrange probably isn’t the best function to use for what you’re trying to achieve!
I’m trying to think if there is a way using a different function, but without seeing the spreadsheet it’s kind of difficult! If you’d be willing to share the sheet, or a copy of it, then I’d be happy to check it out and see if I can find a solution for you.
Ed
Hopefully Jenny gets this reply – Ed is absolutely correct that they way you are going about it, isn’t what importrange is designed for.
One option is to bring the data in as is into sheet 1, then on sheet 2 if you are good with functions (e.g. Rank, match, offset) in spreadsheets you could get this to sort the data for you – however you won’t be able to add your own data into this table, because as soon as another entry comes in, the order changes and your comments would be on the wrong row.
Eeek sorry, I just tested it again, the new row does appear after all, but doesnt insert a new row in the employees linked doc, so the rows are scrambled..
Hi Jenny – I have just replied to you, via a reply to a response from Ed – can you check you picked this up.
Hi Dave
Is it possible to import data from a spreadsheet with all the formats (including colours / fonts etc…) to another spreadsheet
Not that i am aware of, you can only pull data through not formatting.
Thank You, even though this is 3.2 years old Now It was very Helpf for me trying to figure this out
How to pull data from more than 2 sheets into one sheet
Hi Hema, are you asking how to pull data from 2 sheets to 1 sheet within the same workbook or from different workbooks?
i need to do pull the data from different work books.
You can use more than one importrange to do this, what you may find easiest is to have 2 sheets in your destination workbook, each with a single importrange pulling data from a different workbook, then have a third sheet which merges the 2 sets of data together.
I tried this. but while selecting Add-on—>Merge sheets—> Here i am selecting the Main sheet(For consolidated data).
After it is asking for Select the lookup sheet….So here i can not able to select more than 1 file.Please let me know how to select more than 2 sheets.
Hi Hema, you dont need to use a “merge sheets” add on – you should be able to use a simple = function to pull data from 1 sheet to another. If you want to actually merge the data you need something slightly more complex than i can advise here.
Hi Dave Foord,
I need to merge the data from different sheets( approximately 20 work books).Type of data is the same. The same no.of columns.so, i created one consolidated work book. Then with the help of importrange , imported the data in one work book but different sheets.After that i can not able to consolidate in one sheet can u just help me here.
Sounds like you have a lot of data to merge, and something that you need to do correctly otherwise you could end up in a mess. I have exhausted the help i can provide here via this mechanism – if you have a budget, and a few spare pounds, you could hire me for an hour to have a look properly for you and see what your best options are.
Hey Dave,
I currently have a google doc spreadsheet with a master list on the first sheet that includes a ton of different client’s and their respective information, each of which has a different background (fill) color. For example, rows 2-10 are in dark blue, rows 11-13 are in light green, etc.. Because we are constantly inserting and deleting rows to keep up with our client’s needs, their is no set range of cells for each client, with the only visibile separation being the background color of those cells.
I am looking to create a seperate tab (sheet) for each of the clients that mirrors the master list – so that as changes on the master list take place, they reflect on the subsequent tab for that client. I have done a ton of research and I keep coming across the importrange function, although it seems to me that you need to have a set range of cells to do that. I’ve seen that people extend the important range past the actual number of cells they currently have to import, so that it would include any future edits- the problem for me is that I have other clients directly below ones that I want to import on my master list, and extending the import range would end up spilling over into the next client. I have seperated it all by color – and I am hoping that a function exists that simply involved importing information based solely on the background color of those cells, which would allow me to add and remove rows of the same color and stay within the same function.
Any ideas?
Thanks in advance!
Hi Noah. Firstly – the importrange is used to import data from one workbook (file) to another. You don’t need the importrange if you are only importing data onto a different sheet in the same workbook (which you are in this occassion) – so you can use a simple = statement rather than importrange.
To filter what to import based on background colour I don’t think is possible without writing some code to do this, and wouldn’t be the best way anyway. One option would be that rather than you manually colouring the rows based on clients – you use conditional formatting to colour the row, based on the name of the client. e.g. you create a conditional format that if the client name in column A (or whatever column the client name is in) is “Smith” colour this entire row green, you then add the different conditional formats for each client (it depends how many you have as to how practical this is).
If you do this, you could use formula and functions to pull the data from the master sheet onto each seperate sheet based on the client name rather than the background colour. It would require some reasonably advanced spreadsheet skills to work fully, so depends on what skills you have. The other thing to note is that you wouldn’t be able to edit the data on the ‘new’ tabs you would only be able to read it – any edits would have to take place on the master sheet – so it depends on what your plan is to do with the data once sorted.
If you want me to look at this further with you and you have a budget I can look at this for you on an hourly basis.
Hi
I´m using a tool that allows you to Centralize a large variety and volume of data in a quick and easy way. Much simpler and faster than Import Range. You can try it out for free here https://chrome.google.com/webstore/detail/import-manager/baljgggfhdleglaneclmjhhmflaombln?utm_source=permalink
Thanks for introducing this to me, this certainly looks a lot easier to use than the importrange feature, I will test out when I get a bit of time. On Sat 11/04/15 10:55 , Dave Foord’s Weblog comment-reply@wordpress.com sent: a:hover { color: red; } a { text-decoration: none; color: #0088cc; } a.primaryactionlink:link, a.primaryactionlink:visited { background-color: #2585B2; color: #fff; } a.primaryactionlink:hover, a.primaryactionlink:active { background-color: #11729E !important; color: #fff !important; } /* @media only screen and (max-device-width: 480px) { .post { min-width: 700px !important; } } */ WordPress.com
Is it possible to add data together from multiple worksheets in the same cell? For example our locations are putting their productivity and retail totals etc in different columns on a worksheet, I want to add those three locations together in the same cell on a master sheet. Can I just use the importrange(“aldkjfaadslfkjd,”Sheet1!b2:b32”)+etc or do I need to SUM the importranges with a semi colon between? I’m racking my brains!
I don’t know if it is possible to directly combine data from an importrange in a single cell – but I wouldn’t do it that way anyway. if you are wanting to add data from say 3 different sources I would use 3 seperate importrange statements to pull the relevant data into the the destination workbook. I would then use a simple SUM function or + function to then add the data together. This is only going to work if the data you are adding is numerical – if it is seen as text then it won’t allow you to Sum or + and will return an error. On Tue 14/04/15 11:59 , Dave Foord’s Weblog comment-reply@wordpress.com sent: a:hover { color: red; } a { text-decoration: none; color: #0088cc; } a.primaryactionlink:link, a.primaryactionlink:visited { background-color: #2585B2; color: #fff; } a.primaryactionlink:hover, a.primaryactionlink:active { background-color: #11729E !important; color: #fff !important; } /* @media only screen and (max-device-width: 480px) { .post { min-width: 700px !important; } } */ WordPress.com
Try Using :
SUM(IMPORTRANGE(“Spreadsheet1-KEY”,”Sheet1!Range”),IMPORTRANGE(“Spreadsheet2-KEY”,”Sheet2!Range”)………..can add more cells in the same manner……………)
Thanks for the swift reply! I’m a bit of a novice so forgive me… Workbook 1 is location 1, workbook 2 is location 2 and workbook 3 is location 3. They have their productivity totals, retail, number of guests and new guests, it’s all numeric. The dates run down the side and those titles along the top and they fill it out daily, and there is a sheet for each month. There is then a ‘Master’ workbook that I want to combine each days location data into one workbook so we can see the results of each day combine across three locations. Should I just turn it into one massive work book and then summarise it on a separate sheet at the end? Rather than import the ranges?
As you have it set up (with a different workbook for each location) you will definitely need to use importrange to summarise the data. You could change it so that you have one workbook with a tab for each location, then a summary ‘master’ tab all within the same workbook. This would work quicker as importrange will nearly always introduce slowness into the updating and loading of the sheets.
If you keep it as it is, I would use the master sheet and use importrange and have a separate sheet for each location’s imported data so effectively you’re having the same set up as your proposed alternative ‘massive work book’ but it would be slower! Unless there are security/confidential data reasons for having separate workbooks, it seems sensible to have all your data in one workbook with separate tabs as you eliminate the need for importrange
HTH
Ed
Hi Ed,
Thanks for your help. I have done that. Created a worksheet for each month in the master workbook pulling in the data from each location and then using a summary page to sum it rather than pull it from there.
G
Glad you have sorted this out, and thanks to Ed for his response – I was going to reply with the same response.
If you are interested in learning Select From Drop Down And Pull Data From Different Sheet
http://www.exceltip.com/tips/select-from-drop-down-and-pull-data-from-different-sheetin-microsoft-excel-2010.html
is there any possibility to import data from my desktop worksheet to google spreadsheet with automatic formula ?
or
How can I update/copy periodically my desktop worksheet’s data to google spreadsheet.
A very good question, to which I don’t know the answer – hopefully someone else can provide a better answer than me.
>
Dear Davefoord,
Can you suggest any website where I can get reply of my query.
Thanks & Regards
Hello Riz, you could use a google-sheets add-on. I’m sure there might be a couple like this one: Data Everywhere”: https://chrome.google.com/webstore/detail/data-everywhere/foenaaepondggfpfonagpmdaggmpdeel?utm_source=gmail
Is the only thing I can think of right now.
Hi hoping you can help. I’m trying to use the IMPORTRANGE function, but to pull across a value based on a formula from another spreadsheet, rather than choosing a specific cell.
For example I have this formula on another spreadsheet:
=large(‘Chester Data’!E7:E172,1)
Which gives me the highest value within the range E7:E172 and returns this as a value into the cell with the formula in.
What I want is it to do this between spreadsheets, though if I put the formula above in my IMPORTRANGE is obviously doesn’t work as the value the formula gives isn’t a cell reference, which is what the IMPORTRANGE needs.
So basically I need a formula that returns a cell reference based on a cell contents, I could then run this formula on the above LARGE formula, give me a specific cell on the first spreadsheet and then IMPORTRANGE this to the next spreadsheet.
Hope that makes sense …
Hello Riz, you could use a google-sheets add-on. I’m sure there might be a couple like this one: Data Everywhere”: https://chrome.google.com/webstore/detail/data-everywhere/foenaaepondggfpfonagpmdaggmpdeel?utm_source=gmail
Is the only thing I can think of right now.
Davefoord, I find this web-support very useful.. So thanks in advance. Even though, I haven’t been able to find what I’m looking in the answers.
My particular question comes across in whether I should use importrange or a different function. My case is as simple as being able to filter Sheet A full of data, with a list of ID’s that I have in a second sheet named Sheet B. So i’m looking to “curate” Sheet A from ID’s that may increase or decrease, depending on the case. I’ve tried to use the function FILTER, but it doesn’t seem to work as my Sheet B (Table of ID’s) has 30 rows and the data sheet A has 800 rows.. so this and the order in which ID’s are organized in the Sheet A, give me back inaccurate results. Sheet A has ID’s that may repeat 10 or less times, which I might need to “remove” or “filter” in order to create some bulk actions after with some other functions.. I Appreciate your time in giving a check on this case. Thanks dave!!
Hi Dave
Quite an insightful video. I am working on something thing similar managing a set of freelancers through individual sheets. I want to create a Master Workbook but there is one issue, all freelancers sheets are dynamic i.e. i keep on adding assignments to them. So i have to create a separate sheet for each freelancer in Master Workbook. Is there someway that i have to only make one sheet in master workbook where i do allocation of assignment and the data replicates on the respective individual freelance’s sheet.
Hi – I think what you are wanting is beyond simple functions and formulas. It could be possible to write scripts to do this, but personally it sounds like you need more of a database or project management tool than a spresdsheet
Thank you!
Hello, my dad has a commercial flooring business. He has made spreadsheets for payroll, multiple types of flooring, material cost, etc. He wants me to make a master sheet that adds all these totals up. Thats not a problem. I did the = function for each sheet total. As all of them are done weekly. My problem is he uses the same templates to fill his weekly data. Saves a copy and clears the data for a new week. This master total sheet he wants to display all weekly totals for the project. Is there a way to make this automated in the sense that the data is locked in the master sheet once cleared from weekly sheets and can it move on to the next week by itself? Sorry for the book. Just wanted to be detailed.
Hi Danny – Yes there are probably better ways to do this, than your Dads current methods. My approach would be to create a weekly template, (a sheet called template) and then copy this sheet 52 times (1 for each week of the year). I would then name each sheet “1”, “2”…”52″ You can then use the technique that I describe in https://davefoord.wordpress.com/2015/04/16/how-to-display-the-sheet-name-in-a-cell-in-an-excel-spreadsheet/ to have the sheet name appear in a cell in that sheet. You then create a front dashboard sheet that has formulas to pull the relevant data through. The fact that the sheets are named 1,2,3 etc if you are clever with your formulas you can sort of automate this process (so you don’t have to enter the exact formula each time which would take ages). You can also create hyperlinks from this front sheet to the relevant weeks sheet – so when your dad comes in, first sheet is the dashboard, he clicks on the week number he wants (with the date ranges listed next to it) – this hyperlinks him straight to the correct sheet.
There are various other ways you can do this, but it all depends on your level of Excel skill/knowledge.
Thank you Dave, very helpful.
I have a different problem to solve. Maybe you could help me.
I have a spreedsheet in google doc with 5 columns. Daily I’m receiving an email (in gmail) and I am copying the emails content (showed as a table) in the above google doc. My question: is there any script or process to avoid me to manually open the email, copy the content and past it in the sheet?
thank you in advance.
Giacomo
Good question, I expect there is a way to do this, but I have no idea how I am afraid. Sorry cannot be more help.
Thank you anyway. Let’s see if anyone here would help me
Dave, thank you for your time in answering all of our questions. It is much appreciated. 🙂
I, too, have an issue with Spreadsheets. Working at a small school, we have to churn out Progress Reports for our students each month. Info like grades, attendance points and teacher comments are located in other documents, and all must be compiled on this one document to distribute to students.
While we normally do the painstaking task of entering the info per each Progress Report (which can take longer than a day), we need a way to streamline this process. Other comments here seem to be along similar lines, but I haven’t made any progress.
I’ve tried importrange functions, but honestly am having trouble getting multiple docs to communicate.
And if I’m referencing other docs, do they all have to be in Google Spreadsheets to be effective? Or could I have Spreadsheet info be referenced on Google Docs via an = function?
Yes it is possible to do what you want, but you really want a database driven system – not lots of interlinked spreadsheets. Import range is good for pulling data from one workbook to another, but when you create a complex web of such books, it only requires one to get deleted by mistake and the whole thing crashes down.
I would echo Dave’s comments. I’ve tried to create an intricate web of spreadsheets (Around 6-7) and it is annoying when they don’t talk to each other… more often than not it’s Google’s fault rather than something being deleted or it being ‘user error’ though. Frequently you have to change formulas before Google will catchup with what you’re doing. (e.g. Instead of A:A, you have to put A1:A9999… and then next time it errors, you have to put it back to A:A)
Responding to the question though Colby, I think more information is needed, or perhaps an example sheet we can look at to reference when you explain.
One thought though is to use a mailmerge rather than importrange. If you have data in spreadsheets, and all the necessary data needs merging into one document per student, that may be a better way to go. Would be happy to talk you through that if you wish… or at least have a go at getting a better solution for you!
Hi Dave. Very useful video, but I have one problem. My Google sheet doesn’t have the word “key” in it. Does “key” have to be part of the sheets address for this to work?
Hi Kevin, I don’t really know. I haven’t looked at this in quite a while so not sure if Google have changed how things work.
Hey Dave,
I don’t have a question for you, just wanted to say thank you for answering all these questions. It’s really rare to find someone who takes the time to do this.
So, thanks man, you’ve been an amazing resource!
Cheers
Hi Dave, I’m an English teacher who is very much out of her comfort zone…but am trying! Basically, I have responsibility for managing the GCSE curriculum and the staff that deliver it. Following half termly review meetings with each member of staff, I generate a series of deadlines/ focus areas for each teacher that I want to share with them and for them and me to be able to edit. Ideally I’d like to see all these deadlines on one master document (with each teacher having their own tab), but don’t want staff to see other people’s deadlines. Having already done a bit of trawling, am I right in saying that it’s a dynamic relationship I want between the two docs? Anyway, I’d be very grateful indeed for your advice. Is what I’m after possible or am I persuining in vain?! Many thanks, Beth
Hi Beth, you are on the right lines, but I think you may need something a bit more sophisticated than linked spreadsheets, unless you are very skilled with spreadsheets to set them up correctly.
Hi Dave…yep, that’s the problem, I’m not skilled with this at all! So, could I go down the route sending it to individual staff with all the other tabs hidden apart from theirs? Is this possible? Thanks
Sorry, I missed out the part that I’m presuming I’d need to have multiple documents – a master document where I can see all teachers’ tabs, and separate docs for each teacher that just contains their deadlines? I think my question is (!) can I link them together and for changes to be made on both? Thanks…
Final thing I promise! Am just thinking out loud….As an alternative can I simply share the entire master document with each member of staff but simply hide certain tabs to them (which contained the other teacher’s deadlines)? Thanks, B
Hi Beth, yes you are thinking through the possible options which is great. You could try making sheets visible to only certain people. I tried this in the past and it wasn’t 100% successful, but it may work now. If as a school you have office 365 then OneNote may do what you want.
Thanks for this – really appreciate your speedy response. Will see if we’ve got access to OneNote. In the mean time I think I’m just going to have to sacrifice the idea of having a master document and instead do individual spreadsheets for everyone. Thanks, B
OneNote is the easiest solution for you, if you have office 365 it is free, and the tool you would want is the classnotebook https://www.onenote.com/classnotebook
You could also use Excel possibly, it would very similar to Google but everything would be securely stored within your organisations system, which I think would be better for this, each teacher would have their own file, you would then have a master file that could at least read everything that the teachers are entering.
I am using this for some google docs at work, but when I move something on the google doc that is feeding over to my personal doc, the lines on my personal doc do not move with it. Is there a way to change that?
Hi Tarah, when you say move things, do you mean change the structure or layout of the sheet, or simply change the text in the cells? If just text in cells, you should be ok if set up right – if changing structure you would have to recreate link in personal doc
Hi Dave, I’m using the importrange and been happy with transferring data’s , My challenges is how do i capture a report out of it? For instance i have a master and a sheet 1 with 2 separate work book. on sheet 1 every time i edit it it will show up on my master. my question will be when i edit it the second time (sheet1) it shows on my master but remove the first edit. is there a way for the first edit to stay? and second edit will go down on the next row cell?
I’m using the importrange and been happy with transferring/Importing
data’s , My challenges is how do i capture a report out of it? For instance
i have The Master(my report) and a sheet 1 (template use by co-worker) On
sheet 1 let say they edit it the first time it will show up on my master.
my question will be when they edit it the second time (sheet1) it shows on
my master but removes and replace the first edit. is there a way for the
first edit to stay on my Master? and second edit will go down on the next row cell?
Hi Dave – is there any way to bring data into another worksheet based on criteria? For example, I want to bring over all rows from a sheet where the value in column A = “123”? Thanks for your help!
What I would do is bring all the data across into a separate sheet in the destination workbook, then use things like if statements to pull the data you want into your sheet. You can always hide the separate sheet once set up.
I did try to do but I don’d find the work KEY… Below is my complete googledrive sheet link.
I would request you
https://docs.google.com/spreadsheets/d/1a2akfzZPUo2MVUp3BHryQt6aHDznuCnyrDInibc_sQc/edit#gid=0&vpid=A3
I agree that I cannot see the word key in the link, I would guess that the key in your case is 1a2akfzZPUo2MVUp3BHryQt6aHDznuCnyrDInibc_sQc (the code between the 2 slashes), as per this article: http://www.coolheadtech.com/blog/use-data-from-other-google-spreadsheets
Hope this helps
Dave
Hi Dave, could you explain to me how I can pull data on seperate rows into another sheet, say for example the main sheet has say football,golf,darts,tennis all on that sheet and on numerous rows, how do I then pull say all the tennis rows into another sheet and it must update auto when I update main sheet…I hope I have expalined that so you can understand it.. I’m not that PC savvy
Hi Alan, yes I understand what you are saying. Yes it is possible, and is something I have done in the past, but it is quite complex as you have to use a mixture of various different functions – more than I would try and explain here. If I get bored on an upcoming train journey, I may knock something up that does this, but I cannot promise anything as really busy in coming weeks.
Ok thought it might be..I run a sports tipping website and I wanted it so that I can just do the one sheet and update the others to save me having to do all the sheets manually but thanks for replying..
Hi Dave, thanks for the intro to importrange! In your initial post you mention the following:
“if you are using this to track student grades, you could have a master sheet that you and other tutors can see all of, you could then create a separate sheet for each student, and pull through only the data that refers to them (you then share that sheet with the student) and they have a live constantly updating record of what they have achieved etc.”
I’m wondering how to go about doing this? Can you use importrange in conjunction with other formulas to select the rows that you want to import? If so, could you give an example of how that might looks?
Thanks so much for your help!
Hey….Thanks for much. Was really held up on this.
First, thank you for the GREAT explanation of how to do this! Second, I am wondering if there is a way to take the information from sheet 1 to sheet 2 IF it meets the criteria of 2 cells. The first sheet has 8 columns. I want all 8 columns to transfer to sheet 2, only if two of the cells are true. Can I still achieve this with the importrange function?
If you are moving data from 1 sheet to another in same workbook, then you don’t need the import range function, you can do this with a combination of standard functions. Yes it is possible to do what you want, but requires a combination of formulas and functions which Is hard to describe via this mechanism – but i would use the ‘if’ and ‘and’ functions to determine which rows you need and have a column in first sheet with 0 for not needed and 1 for needed, and then functions such as ‘counta’, ‘offset’, ‘sum’ to pull the correct rows into destination sheet
Dave, I am trying to import data from one workbook to the other. Instead of importing Cell > Cell. I want to import Cell > Chart. Any idea how I can achieve this?
Cell range > Chart. Basically I have a workbook with all of my data with a separate workbook for all of my charts and graphs. When I update my data sheets, I want my charts to update as well. Having separate workbooks for this helps reduce clutter when analyzing my charts.
Cheers!
What you need to do, is use import range to pull data from data workbook into the chart workbook, you then create the chart from that data. As the source data changes, your graphs will automatically update. Note there can be a few minutes lag time, so isn’t always instant.
Appreciate the response and the fact the upkeep of this thread has been going for years!!
Hello, I find the comments very helpful here! I was wondering if you may be able to help me with an issue i’m having, and have not been able to figure it out. I’m new to Google Sheets, and need some advice. I have a main sheet that consolidates data from other separate google sheets, which I’ve been able to do using query and import range. My issue is that I will be needing to update the info on a quarterly basis while keeping the historic. The easiest is to copy all the files, and have the spreadsheet keys automatically adjusted to the updated file. I’m looking for a way to dynamically reference the spreadsheet keys, but as for now, I haven’t found a solution. Do you know what would be the easiest way to do this?
Thanks for any input in advance!
That is a good question, and sadly I don’t know the answer.
Thanks Dave, for your quick response; I’ll keep digging around for a workaround!
Hi Dave,
I wonder if you’d be willing to help me. I’m very much a beginning to using formulas to customize spreadsheets. I watched your video and understand how to use a basic importrange formula to pull form responses to a new sheet/tab within the same workbook. Unfortunately, I need a bit more assistance and haven’t been able to find anything online to help me accomplish what I need. I think I require a more complicated setup.
Here’s why: When a response is sent to Form Response 1, I will always need columns C-E and G-J sent to the next available row in a second sheet/tab (Named SP16Q4 in the one I linked below). Sometimes, there will be additional columns in the row in Form Response 1, up to AY. In those cases, I need the data within that row from additional ranges of columns (i.e. L-O, Q-T, etc.) each to be sent to a new row in the second spreadsheet in the columns which correspond to the ones populated by the data from G-J. (Whew! I hope that makes sense! You can look at SP16Q4 to see what I want it to look like once the the formulas are working.)
So, assuming 7 columns in the second spreadsheet (corresponding to C-E and G-J in Form Response 1), I can’t copy one formula to the 4th – 7th columns because the data to be imported to any given row needs to pull from different ranges depending on the form response.
I’m just not familiar with creating formulas and so I don’t even know the basics, let alone how to create a complicated conditional formula like what I suspect I need! Do you mind giving me some advice? If I’m asking too much, I understand. It seems like a lot to me, but I’m hoping it’s easy for someone like you!
Here’s the link for the spreadsheet:
https://docs.google.com/spreadsheets/d/1vDvpoLheLpWsHmLN_leZrTa2bPd1zaGeL4LrDAXYSn4/edit#gid=297795400
Thanks so much!
Amy
Hi Amy – sorry for delay in responding.
I think what you want is more complex than would be worth attempting with a spreadsheet, especially if you are not very good with formulas. Trying to split the data from one row onto many isn’t something I would attempt with formulas alone.
Sorry
Thank you anyway!
Hi Dave,
I have a quick question about transferring data between sheets. So, I’ve managed to get rows to transfer from Sheet 1 to Sheet 2 based on a selection from a drop down menu. However, on Sheet 2, there is a second drop down menu in place of the first, and when the row transfers, it keeps the original drop down. Is there a way for me to transfer only the information, and have it match the destination format?
I’ve been trying to figure this out for a few days now, so any advice you could give me on this would be super helpful.
Thanks!
Molly
Hi Molly – are you using Google sheets, or Excel? and how have you created the drop down menu? Have you used data validation (list) or something else?
Hi Dave,
I’m using Google sheets, and data validation.
Can you send me a copy of the spreadsheet so I can see what is happening? remove and replace any sensitive data obviously.
Absolutely! Here it is:
https://docs.google.com/a/bempdx.com/spreadsheets/d/19t3hS4PF8yFPnaGx2GAQ8Z-kJesGtSalliYUCX9NcwU/edit?usp=sharing
Hi Dave, I have multiple worksheets with similar data but want to combine that data in one sheet. This means I will have 5 Keys for 5 different worksheets. For one key, I was able to accomplish what you demonstrated but how do we get data from multiple worksheets in one master sheet. Help!
Thank you as this did exactly what I was looking for. The video helped immensely.
Hello could you help me out? At my office we are trying to create a formula within the same workbook that will highlight in both tabs if something is repeated. We work at a University and are trying to make it so that if a student’s ID number comes up in both tabs (in the same worksheet) it will highlight both student ID numbers. Does that make sense? Trying to streamline a process and we’re hoping there’s an easy way to do this and you seem like the guy who could help us out. Thank you so much!
Are you using Excel or Google Sheets?
If using Excel – then probably easiest option is to use a formula (a simple = sign) to import the column that may contain the duplicate from sheet A into Sheet B – you then use conditional formatting to highlight duplicates across both columns (I am assuming here that the student ID only appears once on each sheet). Once set up you can hide the copied in column and repeat the process on the other sheet.
I cannot see a conditional formatting option in Google Sheets to replicate this exactly – you may have to use a formula – something like =COUNTIF(Sheet2!$B$2:$B$5,Sheet1!B2) (here the column you are checking is in Sheet 2) this will check if cell B2 on sheet 1 is also in sheet 2 – if yes it will return the number of times that it is. You then use conditional formatting to compare this number – if greater than 1, you have a duplicate – and colour the cell.
We use all things Google. I’ll try the conditional formatting. If I get confused I may ask more questions
It seems like I’m getting an error message. The two sheets I’m trying to correspond are called “1617” and “ID Requests”
Where do I put “COUNTIF” formula? Sorry I’m trying to learn a new skill so I’m not exactly a master with spreadsheets
I have just mocked up a quick example for you which you can view at:
https://docs.google.com/spreadsheets/d/1Ee_NwWuxoS0KD0mtJ3Bi6KeMlHXDLhS9TiHWKmWZQ9U/edit?usp=sharing
You should be able to save a copy to your account and then you can take it to bits.
So – the ‘Check if duplicates’ column is counting how many times each item appears on the other sheet – the $ signs are important here – as when you copy formula down, this stops this range from incorrectly moving down as well.
There is then conditional formatting in column B – this is checking if the equivalent number in column C is greater than 0 (e.g. is a duplicate) and if yes colours it green in this case.
So I get what you’re saying now but what I’m running into is that I can’t apply that for an entire column instead of line by line. Ideally what I’d like to do (but not sure if google can do this) is anytime an ID number shows up on both tabs, to highlight them. The reasoning for this is we send things out through campus mail and it would help us to do both paperwork out at the same time instead of sending things out from the date from one tab and then going to the other tab and sending the same students another thing on another day. Does that make sense? I’m not sure if Google has this capability but it would help us haha.
With the formula that I shared with you – this searched for duplicate IDs in a single column of the second sheet – if you want to extend the range to search more columns you just change the letters and numbers (the ones with the $ signs) to include more columns and rows.
Would this do it?
Hello,
first thank you for your article and for the questions & answers in the comments (which are very interesting), I have a question :
I would like to import automlatically datas from a sheet to another but in a specific manner : each time a user creates a sheet and fill it(there are 3 columns), what is in the 2 columns should automatically be imported in a new column in a kind of summary sheet. I had thought about Importrange but finally I think I should better write a script ?
Having begun to write this script I’ve a problem referring to the length of the columns : how can I import datas in a way the length of the column in the summary sheet matches the exact length of the columns (sum) of the first sheet, should I count the number of cells of each column and make a loop ? Thank you in advance
Writing scripts is not my thing I am afraid, so cannot help here.
Hi Myrium – my script writing ability is very limited, so I am afraid I cannot give you an answer to this one.
Hi There,
I”m sorry if this has been answered in previous comments. I am trying to use import range to create a new spread sheet based off of a master spread sheet. In the second spread sheet, I want columns A and B to be imported lists of products and SKU’s, but then I want to be able to add additional values in columns C,D,E that will correlate to the data in columnss a & b, however, when I add new line items of products into the master sheet, the second sheets column a & B shift , but the information no longer lines up with the appropriate products. Is there a way to link columns c, d, e with the correct cells, so that as the products are added or rearranged on the master, the appropriate information in the following cells is rearrange/moved as well. thanks!
The simple answer is that it isn’t easy and I wouldn’t recommend trying this. What you have described would be much better achieved with a database rather than spreadsheets.
If you do want to do this with spreadsheets then the only way that I can think of is to have an extra column before your current column A – and in this have a unique keyfield – you then have a second spreadsheet which has the same keyfield column and your data for columns C, D and E and then you have a third spreadsheet which pulls the data from the other 2, you then use a combination of functions (vlookup, offset, match, etc.) to arrange the data as you want it. This spreadsheet would however only be for viewing purposes – you would always have to edit the data in the other 2 sheets.
Dave Foord
>
Hi Dave, here’s one I can’t find the answer to that perhaps you might be able to shed some light on. I realize this is an old post so fingers crossed.
I’m composing a simple time sheet with start and finish time for clocking hours. It also tells the guys how much they have made on any given day/week and then what they can expect less tax. This all works fine, what I’m trying to do now is create a summary for each guys hours per week in a separate sheet by using import range. What I can’t figure out is how to make this automatically happen each week without having to reference the exact cell in the relevant spreadsheet for each pay week. Each person has their own identical worksheet as opposed to a separate tab in the same workbook seeing as they would then be able to see each others hourly wage and this brings up privacy issues. Otherwise I might have been able to swing it.
Any thoughts?
Hi Adam. This certainly sounds achievable, it just depends on how your individual sheets is structured.
If there is a row for each day, then each week will be the equivalent of either 5 or 7 rows I guess. What I would do is use the importrange to pull in the entire sheet for each person into one sheet (which may be hidden), then use formula/functions to identify the information that you want into a separate sheet in the same book. Some functions that you may need are:
Now() returns todays date
Int(Now()/7) divides that date by 7, to give you a week number
Weeknum (see https://support.google.com/docs/answer/3294949?hl=en ) could also be used to do the same thing.
Offset (See https://support.google.com/docs/answer/3093379?hl=en) allows you to offset your range by a given amount.
So If you have a 5 day week, I would have 5 separate offset formula (one for each day) each on their own row – using a combination of the above, this would offset by the week number multiplied by 5, to find the correct data for that week. You then carry out whatever weekly analysis you want on that data. When the now() moves into the following week, the sheet automatically updates.
You would have to test things out thoroughly and possibly tweak your formula by adding or subtracting something to account for 1st Jan starting on a different day of the week.
Once set up for one person you would have to duplicate the ‘file’ for each person, and then change the importrange key accordingly.
Hi Dave,
Check out the google sheet add-on Import Sheet (https://importsheet.com/install) which can also be used to transfer data between two sheets without some of the problems of =importrange() such as the persistent “…loading” message or data errors, and with some additional features.
Would love to see your feedback on it.
This certainly looks very interesting, and I will have a look at this when I get a chance.
I have had a look at the importsheet tool, and it looks really nice – I have written a blog post on this, https://davefoord.wordpress.com/2016/10/26/automatically-pull-an-entire-sheet-of-data-between-google-spreadsheets/
Hello Dave,
I am using this sheet to manage a job schedule that filters over into sub-sheets. So I fill out the master and it automatically filters into three different sheets that are organized by department… simple enough. The problem is the jobs are color coded. Coding is random and is used just for identifying which department the job is currently in. The problem is the colors don’t carry over into the sheets that the master filters too. Any suggestions?
What I would do is use conditional formatting to add your colours – you may be able to use existing data to identify which colours to use – or you may need to add an additional column to your master sheet called ‘colour’, set this up with a dropdown list of the options that you want (e.g. red, green, blue, grey etc) – then on the destination sheets (and the master sheet if desired) you set up conditional formatting rules – with one rule for each of the colours in your dropdown list.
I cannot get the importrange function to automatically fill when I expand the cells or paste into another location. The formula acts like an absolute value rather than a relative value
=ImportRange(“Sheet URL”, ADDRESS(11,5,3,,”New Client”) )
The number 3 is supposed to tell the formula that the column is absolute and the row is relative. however, when I copy and paste, I get the same value in every cell.
Hi Kevin – I am not familiar with the format of the importrange that you have tried to use. As far as I am aware, the importrange function only has 2 elements – the key, and the range – see https://support.google.com/docs/answer/3093340?hl=en what you have tried looks like a different function.
Hi Dave,
This tip was helpful. But my problem is that the url for my Google sheet is https://docs.google.com/spreadsheets/d/1Xm5W_riSRwu4YYAjJY9UjE6Kxbnmu2vZ5Y0_9FOC8Tw/edit#gid=1141747673
Which is the key? I tried “1Xm5W_riSRwu4YYAjJY9UjE6Kxbnmu2vZ5Y0_9FOC8Tw” but it didn’t work.
Thanks!
It looks like what you are using as the key is correct, so not sure what is happening here.
Dave,
I was able to make it work by adding a new step.
The issue was trying to take data, in a column, from one sheet and display it as a row in another sheet.
I imported the information into a separate tab, on the new sheet, as a column using the importrange function: =importrange(“URL” ,”New Client!g11:g22″ )
Once the data was in my master sheet, I was able to transpose it using: =transpose(‘Import Data’!A2:B13)
This allows me to transpose two columns of data for each client into two rows of data.
Thanks
Kevin
I Have created google spread sheet and sent to the concerned people to fed the information. now how can i view the report and download the data
Hi Dave,
I’am trying to pull text or data from a different google sheet if it meets a certain criteria. The twist is that the information evaluated is in one cell and the information I want pulled is in another cell. ie. On one google sheet I have all the letter grades of the class( in one cell) and the student information(in another cell), I want to pull all the students who received an A,B,C- pull their information onto a different google sheet-one for all A’s-one for all B’s..etc…
Thanks in advance,
Brad
Are you wanting to move the data to a different sheet in the same workbook, or a different sheet in a different workbook? if the latter I would initially use importrange to pull all the data across and then use functions to sort it out into the different grades.
There are different ways you can do the sorting out but all too complicated to describe here. I will however when I get a chance do a screen recording of the steps and add it as a new blog post to this blog.
Dave Foord
>
Hi Brad – have a look at https://davefoord.wordpress.com/2017/03/29/spreadsheets-how-to-sort-data-onto-sub-sheets-based-on-values-in-a-given-column/ is this what you are wanting?
Hi dave, is there a way to import a specific range of data on your master sheet. E.g I only want data imported from the master sheet that has ‘yes’ selected on certain rows of data. Only the ‘yes’ selected data I want imported to another sheet. Is this possible?
Have a look at my latest blog post – https://davefoord.wordpress.com/2017/03/29/spreadsheets-how-to-sort-data-onto-sub-sheets-based-on-values-in-a-given-column/ which I think is what you want.
Dave Foord
Hi Dave, thanks for that it did up doing what I needed! Now my end result is to get that same thing happening but for google sheets. I believe the certain formulas used do not work once migrated into a google sheet – or am I wrong? Is there any way around it?
Thanks again,
Mel
The function to pull the sheet name into a cell is different in google sheets, but if you look at the blog post on this, it gives the google equivalent either in the post or in a comment, apart from that everything else should work.
Dave Foord
>
Thank you! This is extremely helpful.
I have a question. Often if we’re entering student grades into a spreadsheet we don’t want to share every student’s grades with the whole class.
Say “England” is the name of a student.
Is there a way for the “shared” document to pull data only from rows where column A has a cell with “England” entered in it? So when we share a sheet with “England” all they see is England in column A, London in column B and Pound in column C, but not the information for Ireland, Switzerland, Germany, or USA etc? So each student could see their assignment data but no one else’s?
Thank you for any insights!
Aubree
Hi Aubree – the simple answer here, is that this wouldn’t be easy (I don’t think), and if you are wanting that level of functionality, I think you need more than a spreadsheet solution for storing your grades. But if you only have a small number of students, then you could use the technique identified here:
Which would create a sheet for each student, you would then have to share each sheet with each student in turn. But I will be honest that I don’t like this solution, as lots of potential for things to go wrong, and if the students are clever, could probably then see other students grades.
This is a great video and I use this action all of the time. However, in paragraph 3 you describe the reverse action “Or you could have a mechanism where each subject tutor has their own spreadsheet to record class grades, then the course leader has another sheet which pulls all of this data into one place, so they can at any point in time see how the class is doing without having to ask tutors to email over their latest version of their standalone spreadsheet (which I see happening all to often).” and I can’t figure out how to make that work. Can you help?
Hi Lauren – to set this up, the course leader has a workbook containing many sheets (1 for each subject). You then set up lots of importrange functions, pulling the data from each subject sheet, into the corresponding one in the course leaders sheet.
hello , how can i copy rang from sheet1 to sheet2 , the sheet1 is updating ones a day , I wand copy that data from sheet1 to sheet2 every day with out deleting old data in sheet2
I am not sure I fully understand what you are asking. If pulling data from one sheet to another within the same workbook, then you can just use an = symbol, if you want the entries in sheet 1, to be recorded as a new row in sheet 2, then you may need either a script writing, or restructure the way your systems works.
Sorry I cannot be more help.
I will try to explain more , I have data in sheet1 that data updating once aday , so I want copy that data and paste it in sheet2 (or another workbook doesn’t matter), but I want if that data in sheet1 updating copy it then paste it in sheet2 in empty cell not over old data
That video has 50% I need to add a code in same scrept in that video to past new data into empty cell
Yes – it is a Google Script that you need to achieve this, not my area of expertise so I cannot advise further, but it should be a relatively simple one.
If you were using Excel, it would be a macro (Visual Basic) that you would use.
Thank you for replay