Google Sheets - Linking Data Between Sheets (Workbooks, Files) & Other Worksheets (Tabs)

in this video I'm gonna cover all kinds of ways to link to data from different tabs or worksheets or different files or sheets in Google sheets so let's just quickly define the difference between worksheets and caps a worksheet see these are two worksheets here or we're gonna call them tabs whichever one you want to call them and sheet or workbook or a file would be when we have this other spreadsheet and there it is that's a whole different file or sheet whatever you want to call it so I'm gonna show you all kinds of ways you can link between different tabs and between completely different sheets if necessary so let's start with this one so I'm gonna go ahead and create a new worksheet here I'm gonna call this one summary and I'll go here and create some psalms for each one of these sales columns so that's that and here we'll do some and select the range over here that's good enough so I have one total here and another total here and I'm gonna move those totals to my summary tab so I'll do people and new people and now I need to link to this cell over here to get this total to 142 11 and bring that over over here and that is age 4 that's the cell right here so to point to that cell I'm gonna go here and do equals and then start by the name of the tab so this tab that it was supposed to come from is called people so I'm gonna type people then exclamation sign and then age for that should link to that worksheet that's the name people then exclamation sign is the separator between the worksheet name the range and we have the range so if I present her that should link to that number now the same way if I want to link to this which is age seven I'm gonna go here but in this case this tab has a space and when you have a space in your tab name you have to enclose the name in single quotes so I'm gonna do this new people in single quotes then exclamation sign and then the range again which was age seven I'm gonna press ENTER and that should return those two so this is this number this is this number I also want to show you how we can link to an entire range of data so maybe I want to bring over all of these numbers instead of just this one cell so for example all of these numbers are located from h2 through h6 so to do this I'm gonna go back here and I'm gonna say new people data and then I'm gonna do equals and then the name of the tab again because it has a space it has to be in single quotes exclamation sign and then h2 : h6 so if I press ENTER right now this will give me this one 11 whatever that is so if we go back here you'll see that it's this number over here but I was trying to bring over all of these so what it's doing it's basically returning just the item in that row so this is Row 4 and if I go back here see this is Row 4 2 that's what I'm getting but what I need is that whole column of values from here so to get that I will have to make sure I put this entire thing in a function array formula and if I do this and press Enter that should bring over that whole column so those are the values and I'm linking to those values here so if you're linking to an entire range and you want to bring over the range from the same workbook a different tab you have to use this array formula to do this when use this array formula function you don't necessarily have to point to a whole different tab so if I wanted just to make a copy of these numbers over here I could just go here and do equals and in this case it's gonna be b4 through BH so that's before column b8 and again because that's a range I have to use array formula to do this so we can also use this pointers like links to other tabs inside of functions like similar to how we did this or a formula here to do that I'm just gonna clear all of that and let's start over so let's say I just want to sum all these numbers from over here h2 through h6 and I want to sum them directly in here so what I do I do equal sum and then again I point to that tab that's called new people and then I do an exclamation sign and then do the range so it was h2 through h6 if I'm not mistaken and if I press Enter that should add up to that same amount and this way I don't really necessarily need to do this sum here at all I can just clear this number from here and still have my total on this tab and now you can see this link doesn't work because we're linking to an empty cell I mean the link still works we're just basically linking to an empty cell now I'm gonna bring that back undo that so that's that so usually when you do this you don't really type this formulas here what you do you simply just select the data but it's a good idea to be able to read this and understand what the syntax is because when something goes wrong you need to fix it and you fix it by knowing the correct syntax I'm gonna clear these and repeat this again without having to type that in so what I do I type equal sign and click on this people tab and click on this number and if you look here in a formula bar see it says people exclamation sign H for I press ENTER and that should return back to my summary sheet with this link so the same thing here equals I go to new people click on this and see it says apostrophe new people apostrophe exclamation sign and eight-seven good that works and finally if I wanted to do the sum I do equal sum open parenthesis and this is when I'm ready to point to that range of numbers from the other tab so I go to that other tab and select the range and if you look right here see it goes H true colon h6 from new people worksheet that's great with closed parenthesis press Enter that gets us the total and finally if I wanted to do the same thing from people I do equal sum open parenthesis go to people and select the range close parenthesis and press Enter and as usual you can see because people tab doesn't have spaces we don't put single quotes in this case all right so now let's link to a completely different file or sheet in case of Google sheets so to be able to do this you need to first go and grab the link for that other worksheet so that's what I'm gonna do I'm gonna just copy this go back here and to do this you need to use a function called import range so let's first of all just do a sum so we can linked to it so I'm gonna do the sum of all of that and this will be our age six cell from people tab so I'll go back here and say other file total and go here and do equals import range that's the function and the first parameter in this function is spreadsheet URL so what I'm gonna do I'm gonna add quotation in this case double quotes paste the link to that other worksheet and quotation again so that's gonna be the link to the file and then I'm gonna do comma and then you need to do again where are you pointing to so if you remember it was h6 from people worksheet and what do you do you have to do it as string or text so it needs to go in quotations again so I'm gonna do quotes double quotes in this case the name of the other worksheet people on the other file and then exclamation sign and it was h6 cell quotation again close parenthesis now when you're linking to other files initially you have to run this and get permission to actually make this happen so when I press ENTER it will not work immediately it's gonna give me this reference error and see it gives me this you need to connect these sheets so you need to now allow access and then there it is we're linking to that other worksheet and we're getting this total now let's say I'm gonna make a duplicate here we have another one here that's called another people and I'm just gonna delete one of these rows so we get a different total and that's h5 now let's try to link to this so I'm gonna go here and do that equals import range open parenthesis then again I have to do a link to the other spreadsheet so in quotes comma and then in quotes again I'm gonna do worksheet name so the worksheet name is called another people another people exclamation sign and then the range is age five close double quotes close parenthesis press enter and that works just fine so notice how when I was doing this other worksheet name I didn't actually do single quotes so when you're using this import range function you don't necessarily have to use those single quotes around the name of the worksheet even though it has spaces in it but if you do it will still work fine so if you do this it will still be okay and it will work so that's fine but for example in this case if you tried to just remove the single codes and press Enter when you're working within the same file this will not let you do that so I'm gonna undo that so you will have to always use the single codes if you're linking within the same spreadsheet but if you're using import range this is not mandatory using the single codes now the same way we can also get the sum without having the sum over here so for example let's actually remove this one that's the sum from here I'm gonna go back now that sum is gone I'm gonna clear that and use the function sum this time to sum up all these numbers from here so for this we'll have to use import range functions so import range open parenthesis and again the link to the spreadsheet goes here comma and then the name of the tab which is called people if I'm not mistaken exclamation sign and then it was H to : H 5 close parenthesis press enter and we have our total one thing to note here if you're doing this for the first time and you didn't run import range from that file already you can't directly just do this you will need to before you do the sum just simply do this import range like this without doing the sum and that will get you that allow access box so you have to do that first before you come back and do some to get this all summed up together so you can also simply just bring over the data from the other file so again that will just go here and instead of doing the sum we'll just do import range now remember when I had to link to a range from the same workbook I had to do that array formula here when I use import range I don't need to do that so if I press ENTER right now it will bring those numbers over like this and as a matter of fact we don't need to do just one column when we do this type stuff we could do multiple columns so I could do this entire data from a2 through h5 so if I go here and do a 2 through h5 you will see it brings over that entire range of data to this workbook so one last thing I'm gonna talk about before I end this video is that when you use import range function you don't necessarily have to do the entire link like this and get your functions so large so what you could do you could just use the ID so the ID part is starting from cities 15 so right after this D slash if I just remove that first part and then going after the slash at it so I can remove that too and just keep that middle part that will be the ID of that other spreadsheet and this will also just work fine so you can also do this and that should do it for this video thanks for watching please subscribe and I'll see you in the next one

Channel: Learn Google Spreadsheets

Views: 348,408

Keywords: Google Sheets, Linking, link, Data, Between, Sheets, Workbooks, Files, another, other, worksheets, tabs

Id: iGvvK8O5BpQ

Length: 15min 46sec (946 seconds)

Published: Tue Sep 17 2019