Google Sheets - Link Reference Other Sheets (Spreadsheets)

in this video we're going to go over how to link or reference another Google Sheets spreadsheet using import range function and some extra tools and we're going to do it in a way that doesn't break and to demonstrate what I'm talking about let's first of all just start with this we have this spreadsheet called first sheet and here let me just rename this tab it's called sheet one I'm going to double click and call this one main data now in this tab I'm going to add some numbers here so let's just call it amount and add a few numbers here and we'll use those numbers in a formula on a different sheet so first of all let's just change this to currency so now let's try to reference the these numbers on this second sheet and to do that we're going to have to use import range function so I'm going to go someplace here and do equals import range and the first argument here is going to be spreadsheet URL so I'll go to the second sheet copy this URL on top this one go back to this and here in codes I'm going to paste that spreadsheet URL so that's the first argument comma and the second argument here is range as a string and that also has to be in string format meaning in quotes so I'm going to go double Cotes and for now I'm going to close parenthesis and within those quotations we're going to reference the range and the range if I go back to this this is D2 through D4 and the tab is called main data so I'm going to go here and do main data which is the name of the other tab exclamation and then D2 through D4 like so now at this point if I enter first I'm going to get this ref error initially because we need to give permissions to access that first sheet and to do so we simply just click on this ref and this pops up so we're going to click allow a access and now we have access to that range and you can see we get our totals now I'm going to double click here and there is a couple of things I want to go over here number one this URL doesn't have to be the entire URL for the other file you can simply just do the ID of the spreadsheet and the ID would be this part right here see this after this D forward slash going after this forward slash edit so basically I can get rid of this part and I can get rid of this part and just keep this URL of the spreadsheet and if I enter this will still work just fine and this way it's going to be shorter formula now this main data is the name of this tab here this is D2 D4 the range now you can also put this in single codes main data so if you're familiar with referencing you are required to do the single quotations if you have spaces or other characters here but here you don't really have to do the single quotes if I do them it's still going to work just fine but I can also get away without actually doing those single quotations just the name of the sheet followed by exclamation sign and then the range if you want you can also do open references like this like D2D and the idea of that would be if you later basically just add more data to this second spreadsheet when I go back to this it will automatically basically pull that data now at this point you can then use this inside of other functions for example sum so I can go here and put sum around this close parenthesis hit enter and that will just basically sum those numbers and give you the total using the data that's located on this and of course you can see now the total is 254 if I add another let's say 70 here we go back and take a look it will update to 324 so that's great now the downside of all of this says if I actually go back to that first sheet and add a column someplace here and then we go back and take a look at what happens you can see my total is zero because in this formulas when you do this range you basically provide this as a string so essentially this is just hardcoded D2D from this main data worksheet and because I add a new column now this is no longer accurate because this is going to move to e but our formula is going to have no reference or idea that this actually happened and it's going to stop working as soon as you add or delete a column because things have moved another thing that could happen if I just delete that column you can see it's now it's going to work again because it's D column if I go back here you can see it works but then the other thing that could happen if if you change the name of this let's say I call this main data 2 once again if I go back to that second sheet and take a look you can see this is going to be broken now as a reference because there's no main data anymore so it's not going to know to update it it's still going to look for that main data and it doesn't exist and we're going to get this error so I'm going to go back and delete this so that brings us to how can we avoid this problem now you can see this still giving me the reference and sometimes it just doesn't update very fast so I should be able to just refresh this whole thing and you can see it's fine now now that brings me to what can we do so this doesn't happen and to avoid this problem that I'm facing right now what I'm going to do I'm going to go to the first sheet and I'm going to use a named range so what I'll do I'll select this list and go to data menu and named ranges and here it's going to ask for a name for this range so I'm going to give this some sort of name I'm going to call this sales data no spaces or special characters here and then this is the range we're pointing to I'm going to press done and this is a named range if you're not familiar with named ranges you should probably watch a separate video just just about named ranges to learn about them but right now I have this sales data named range and if you already had one this is the screen you're going to get first so you're going to have to click add a range and then do the same thing I'm going to cancel this because I just did so now I have this sales data named range so because of that I should be able to go back to this second sheet and open this and then change this to sales data like this and notice I'm not doing the name of the sheet exclamation sign or anything just the name of that range and now if I enter I should be getting the same thing but here's the interesting part now if I go to that first sheet and add let's close this add a column over here and then go back and take a look you'll see this still works just fine because even though it moved the name is the same and because of the way named ranges work when I add a new column this first Spreadsheet will automatically update the reference to this data and this way even if I change this name of the sheet to let's say main data 2 or whatever again it doesn't really matter because we never even use the name at all here so this still works it doesn't fall apart it doesn't break down and we're fine even though I'm going to add a column here or maybe even add a row on top here or whatever this is still going to work just fine now of course if you want to continuously just keep adding to that list then you need to probably Define the name a little differently so you would go here named ranges and this that range so I'm going to click on this and we can update this and one way is to well the thing here is that if I just try to drop the end of the reference like this hit done what it's going to do is see it's automatically going to go to line 1,1 it doesn't allow you to actually use open references inside of your named ranges but what I could do is do something like this just use the whole column F hit okay and that is fine so now I'm basically pointing I'm going to delete this first line I'm pointing to this entire column F from top down and with a setup like this well it's going to ignore amount because some function ignores any text and as a result what's going to happen see if I look here this is my 324 now if I add another 76 here it's still a part of that F column so if I go back and take a look here see this 400 works fine and once again because it's a named range I should be able to delete columns at columns whatever and as I do that if I go back and take a look this still works just fine no problem and that's how you reference to different spreadsheet in Google Sheets in a way that doesn't break and your links to other spreadsheets still work just fine even when you start adding deleting columns and changing names of sheets and that should do it for this video thanks for watching please subscribe and I'll see you in the next one