How to Link Data Between Sheets and Spreadsheets in Google Sheets

hi there jamie here today at teachers tech hope  you're having a great day today i want to   show you how to link data in google sheets so  what i mean by this for an example if you have   multiple sheets like this i have four in this one  spreadsheet where you can pull information from   one of these sheets and then put them on one maybe  to sum something up so for an example you can see   if i change this number right now it's 29 if i was  making this 10 and go over to my main sheet this   changed here so all these numbers are tied to the  other sheets so not only that i also want to show   you how you can link spreadsheets so you can have  multiple spreadsheets and be pulling information   from them into one other spreadsheet let's get  started how you do this today on teachers tech if you want to follow along with this today  i'll put a link to this spreadsheet and others   down below in the description and i'll i also will  time stamp everything so you can jump to different   parts of the video so what i want to do first  in this spreadsheet i have four sheets you can   see it says all schools copperfield fish crete  and hawkwood i have some information in these   i want to take the totals here of each one and put  them into here so i want them to reflect if i make   a change over here it will reflect the totals over  here now first thing i just want to mention if

you're new to linking anything to do a simple link  on the same spreadsheet of a cell if i just go   ahead and put equals anywhere and then i just type  a cell so like this one is e5 so if i go and type   e5 here like so and hit return notice that these  two reflect so if this number i'm going to change   this number but you're going to see this number  change this number changes because they are length   so that would be a simple link on a spreadsheet on  a single sheet so i'm going to go ahead and delete   that but what happens if i want to pull just a  single cell from a spot in here so we'll take this   for example this b2 from copperfield onto this  one so what we need to do is i'm going to start   with the equal sign again i need to write the name  of the sheet so this is called copper field so i'm   going to go ahead and just spell this out like so  at this point i need to put an exclamation mark in   just like that and what was the cell well  that was b2 so i'm going to just type   b2 whoops just like this b2 and enter and notice  that's six so if i go over to copper field   and change this number so if i change this to  10 now you'll see that this reflects that change   now here's something to note in this case where  it says fish creek if i was going to do the same   thing i would go ahead and put my equals in  but if there's a separate two separate words   like this there's fish and creek with a space  you have to do this a little bit different you   need to start with the single quotation you can  type fish creek like this and end the quotation   put your exclamation mark in this case i'll put  b3 so i'll just put b3 like so and hit enter and   there's the two so remember if i don't put those  there so if i go back to this and i'm just going   to make a change and i'll get rid of those and  i'll show you that this will be an error so you   need to put if there's a space on your sheet  between the two different ones you need to put   the that in the single quotation mark on each  side of the word so that's just my tip for   linking single cells from sheet to sheet now  on the next step what happens if you want to   link a range so when i'm talking about a range  range from one spot to another

from this cell to this cell so in this case you  look it's b here 5 to e 5 and i can also look   right up here it shows me the range that i've  selected if i go down like this it's b2 to b5   i can place i can link right on the same sheet  if i'm on and i can go ahead and use a function   so when i say a function if i go ahead and use  the equals and start typing array formula this   is the function i need what allows me to do  if i select array formula and go grab a range   and hit enter it links this on the same sheet so  if this changed here to let's say two you can see   it updates automatically like this now i can go  the other way too so if i was going to type array   oops we've got to make sure we put our equal sign  in array formula and i'm just going to highlight   it this way you can see it goes across and  these now anytime i make any change any of these   it will update so i'm just going to go ahead  and delete these i just wanted to make sure   if you're new to it i wanted to show you that  so in this case if i go to my beginning one   i want to take these numbers right here so  this is b5 to e5 over here to this copper   field i need to start with my formula so i'm  putting equals in and i start typing array   and this is the one array formula i need to type  the name of the sheet that i want copper field   just like this and then i need to just put  the exclamation mark again and what's the   range i wanted b5 colon e5 just like  that and i'm going to hit enter and 22 23   12 16 22 23 12 if one thing changes here  i'm going to make this a 10. this is 18   and you can see this updated so that is linked so  if i want a link and this rule applies to what i   showed you before this fish creek one in here  if i go here this time and put my array formula   in like so i need to use my single quotation  mark type fish creek and my single quotation mark   add my exclamation mark and it's going to be the  same range that i want so it's going to be the b5   b5 colon and then i'm going to put my e5 in here  so just like that and 6 10 15 7 and you can see   that is right here so these if any  changes happen in here it will adjust over here   so go ahead and try this last one here  to see if you can get the information   coming across here so as i showed you in the intro  this time i'm dealing with a number of different

spreadsheets and i have them all open so i  have this linking spreadsheets here this one   titled and i want to take information from  this spreadsheet and this   spreadsheet and put them all in so kind of that  bottom the totals here of the sales into over   here so it's a little bit different how you do  this now if i look at this spot this bot here   i need to take the information from this worksheet  here this sheet on the copperfield spreadsheet and   what i'm going to do is use a different function  and it's going to be import range so i'm going   to go ahead and hit equals and just type import  and it's going to be range here it is right here   so what this one does is imports the range from  another spreadsheet so where do i want this to   come from well you need to copy the url of that  spreadsheet so if i go over to copper field here   i'm going to select this i'm going to go ctrl  c and go over here but before i paste it in   this time i need to use the quotation marks the  double quotation marks so i'm going to start this   and then i'm going to go ctrl v to paste it in  and it's going to walk you through what to do   here step by step but i need to end this with my  other double quotation mark at this point you can   see that they tell you to add a comma and where  is this going to be coming from so i have the   link to the sheet this over here i need to look at  what did i call this one well this one is called   sales so i have one called school info i have two  different sheets on this i have to make sure i   tell it what one to come from so this is called  sales now in the range is going to be the same   it's going to be b5 to e5 so if i go over here i  need to start with my double quotation mark again   and i'm going to just type in sales like  so and i'm going to add my exclamation mark   and now the next thing i have to do is put  my range in so it's going to be my b 5 colon   2 e 5 like so and i'm going to end just like that  so i use my double quotation mark again i can end   my bracket and hit return and notice it says  reference here now the issue is you just have   to allow it access so if i go and just click on  allow access you're going to see it populates   over so it's not that much different from taking  it from the from individual sheets into one other   sheet in the same spreadsheet and if you're using  this one i'm going to do it one more time so if i   go ahead and just type import range right here and  this time i'm going to take from the fish creek   and you don't have to change anything anything  special with this one being this one i made it   into two words to show you if i go ahead i need  to copy this over so i'm just going to go ctrl c   and go back over here put my quotation mark my  double and paste this in my double quotation mark   my comma and this one i have called sales test so  i have to start my double quotation mark again i'm   going to type sales space test and i can put my  exclamation mark in and what's my range well my   range is b5 call it e5 like so and i can go ahead  and my double q exclamation mark and my bracket   and hit return and that time it didn't ask me for  access because i actually already did it prior to   test it and i allowed that one access but you can  see if i click back into this i can go through   and make any changes to this so if i go ahead and  copy this down what i like to do sometimes is so i   copied a lot of the formula down to this one it's  the exact same one but if i wanted to go to this   hawkwood one i could go and copy this one and go  back to linking it and i could just highlight this   right in here and i'm just going to paste over  it and i also know that it's just called sales   in here so i'm just getting and i'm gonna hit  return and it's gonna save reference again   to allow my access and so that's just a little bit  of a shortcut and you can see if i go over here   13 13 16 and that's what i pulled over so  those are different ways you can link your   data so whether it be cell to sell from sheet  to sheet from worksheet to sheet and spreadsheet   all the different ways in google sheets so  i hope you like this walkthrough of how to   do this let me know what else you're looking  for in google sheets and other things thanks   for watching this time on teachers tech i'll see  you next time with more tech tips and tutorials