Google Sheets - Named Ranges, Dynamic Updates, with IMPORTRANGE, Other Sheets, Arrays - Tutorial

okay so in this video I'm going to cover named ranges so we have some data here on this 2017 tab basically some sales data and let's say on this master tab we would like to just get total sales so what we usually do we do equal sum open our parentheses now we go and highlight all of our numbers so I go into 2017 tab and I'm gonna click on this first number ctrl shift arrow key down to highlight those numbers for good practice maybe f4 but that part doesn't matter in this particular case and close our parentheses hit enter that gives us the total from the other tab a lot of times it becomes really difficult to do this selection because you may want to combine multiple tabs together and things like that we'll see an example of that for that we use named ranges so instead of using this whole 2017 e 2 e 3 10 we can use a name range to simplify the syntax here let's see how we can do that so I'm gonna hit escape I'm gonna go to 2017 so what I do before I do any formulas I just go and select all the numbers like this control shift down so now that this is selected I want to just go ahead and name this range so there are a few ways to do it one way you can just right click on it and then if you scroll down here you're gonna find something that's called define named range so if I click on that this is gonna pop out and here you give it a name so I'm gonna name this make sure you don't use any spaces in this names and also don't use a name that's already available so if I try to name this like b300 that cell already exists I don't want to do that anything else you can do so I can just call this sales numbers or something like that there we go it done so now we named this range so this point I can simply go to my master tab and instead of doing some and doing that whole selection thing I can simply just say sales numbers and close my parentheses hit enter and I'm gonna get the same result so now it's simplified so across this entire worksheet I can anywhere just use this name and it will refer to that particular range of numbers so let's see a little nicer example of this so I'm gonna delete that if you want to get rid of that named range by the way if you close this and it's gone you will have to get to that named range again so if you go back under data named ranges there they are I'm gonna click edit and then click on this little delete button hit remove and it's gone close and now that name range doesn't exist okay so now let's do it for multiple tabs example if I wanted to combine 2017 2016 and 2015 together one way I could do it I could just create my array and I can go here and basically just do from here control shift right down that's the first range right and then the semicolon I go to 2016 clicking the first Monde control shift this doesn't work with ctrl shift right down and that's yet another reason to use named ranges but again we'll just fix this later semicolon again and then I go to 2015 and again because ctrl shift right down doesn't work on this next tabs I'm just gonna do this close my curly brackets hit enter that should combine all of those tabs now together the problem here is that one thing I didn't do is that I wasn't able to just select all the way down because I have a lot of data so now I have to go manually update this so for example this one has two thousand four hundred and twenty two rows so I will have to go and change this 2422 or whatever that was then I have to go to this one and figure out how many we have here two thousand two hundred seventy-nine so go back here and do two 279 so again if you're trying to learn how to combine tabs together I have a better video covering that this is about named ranges so now I have all of those combined together but that was not very easy to select especially if I have let's say ten years down here this is gonna be very very complicated to go and select and then go back and figure out how many things we have in each one and get that all together so let's try to do the same thing using named ranges so what I'm gonna do I'm gonna go to 2017 just select my data and give it a name right click and define named range and I'll call this year 2017 hit okay so I'm gonna go now back to 2016 simply gonna just highlight this click add range this is another way by the way to do it so you don't have to right-click again year 2016 done 2015 same thing and if you have a lot of them just keep going now go back to my master and when I have to combine this tabs I'm gonna start with my equal sign curly bracket and I have to do my first ring so I'll do a year 2017 semicolon then year 2016 semicolon and then year 2015 and close my curly bracket hit enter see how much nicer that was I didn't have to go to any other tabs I didn't have to select any of those ranges just type those names nicely and there's my data so hopefully that gives you already some preview how you do this and how you use named ranges and why they're actually useful so I'm gonna delete this other things you could do is a lot of times when you do some sort of vlookup formula or something like that it can name your range and use those named ranges to do your vlookup formulas or really pretty much anything you want to do if you want to run the query function on top of this range all you have to do is just go back here and do your function and really what I'm trying to show you is you could use this in any function right that's really the point and for my data I can just type year 2017 there we go comma and here I can do some sort of select statement I can say select all columns and then we want to filter by something I guess I'll figure out the filter after I don't remember the columns comma and we have one row as header so now actually we have 0 because I still liked it starting from here so zero should have been the number but the column I want to filter this by let's say it's gonna be sales we'll do a column so I'll get back here first of all modified as one to zero because we don't have any headers when I did my range selection I didn't include that and then I'll do select e greater than 200 so all the numbers that are above 200 we would like to get those I forgot where key word there we are so now we're getting all sales that are above 200 and again the whole point here is the named range now the only challenge from this is that if you go back and you add more records here add a thousand rows there it's not gonna be included in your named range because it's a set range see the 2017 it says from a 2 through H 3 10 we want this to just automatically expand as we're adding new data so we don't have to go and change our formulas every single time usually in Google sheets you could just if you added this range you could just remove the row reference in the end like this and not include it when you do this in formulas it works right now named ranges if I do this C doesn't take that it just removed what I did so the way to go around that is to just not use any rural references so you just include the whole column like this so now it's gonna take the whole column starting from the top in 2017 which means this labels are included and going all the way down and all the blanks and everything included so now it's gonna be automatically including new entries now going back to this we have to update this because remember I said there are zero headers on top but now because I selected the whole column like this because that's what eight through age means in this particular case it's going to include this labels on top so I do have headers on top so I'm gonna go back and change that so this will be one now the rest just works as is the only difference is if we just go and add something so we'll do one one 2017 and let's do a very high amount here like 70,000 so if I go back and troll down see it's in there so that's our way to make it more dynamic if we have to do that that works so let me actually delete this I didn't really want that you can also do named ranges inside of named ranges if I have this whole table already named this I can still go and highlight parts of that table and name them something different so for example I can go here and get this column and add a range and call this region 20 17 done then I'm gonna do this one so at range and I'll do sales 2017 and you can keep going and include as many columns as you possibly need so for example I can go here and do state 2017 and now because we have this named ranges we could go to our other tabs or the same tab doesn't really matter I'm gonna remove this and we could do some sort of like some ifs formula some range is our sales 2017 comma criteria range one I'm gonna check in regions so I'll do region 2017 and in that region 2017 you know what I mistyped that region 2017 comma and inside of that region 2017 we would like to check if it's Midwestern comma and then we have criteria too so that will be my state so I'll do state and by the way and when you start typing this you're named ranges show up so you can just click on it or you can just go with the arrow up and hit tab and it will select it comma and we will have in Midwestern Illinois I guess it's redundant but we'll do it so here we go that's our total sales in Midwestern region and state Illinois and you can see how much nicer our formula looks with this names because we can clearly see which one of these items is which instead of looking at that whole range that says like the tab name and then exclamation sign and that drange and all that stuff another cool thing is that we can also use this named ranges in different worksheets or different files so if we have this other sheet which is completely separate from this one I can use import range again let me just copy this URL of this file and just go here and use my import range function so we have to do our spreadsheet URL so that was what I was copying as text so in quotes comma now I have to do the range in that so again usually it would put this range in quotes as string but now we already have names for everything so all I need to do I can just do something like year 2017 hit enter and see this is the first time I'm using import grain so I need to allow access to that other spreadsheet allow access and it works and they just move this down a little bit and it's a lot easier to work with this now and it's a lot easier to remember what's the range and refer to it rather than doing a tab name and then all that other stuff we just use a named range so let's do another example here so people like to do vlookups a lot and we'll do viewer cobs using name ranges to see how that works so we have this tab and we have some data and we have stock numbers in this tab and we also have this products tab where we have stock numbers and we have the brand we would like to pull the brand from this tab to this tab before I even start my vlookup I'm gonna go to my products tab and I'm going to just select my Villa cup table or in this case vlookup columns that's gonna be from here through here because we need to start with whatever we're searching we have stock numbers matching so it needs to start with that column and we also want to include brand in there I could probably just also just do this just thinking that I might need the price or the coast later on right click and define a name for this call this product table there it is products done so now I named this table at this point I'm gonna go back under transactions create my Villa cup I'm gonna close this so there's space to work I'm gonna do a brand and equals V lookup so I'm searching for this stock number that's my lookup value comma and this is where we have to do the range which is that table on the other tab and I named that table product there it is it shows up here products comma now in that products table which column was Brent it was the third column in that table 3 comma and finally it needs to be an exact match 0 close this hit enter and this works just like that we can double click this and send us down again if I'm working from a completely different file so let's say let me delete this copy this whole thing okay going back here gonna try to paste it so now I'm going to basically do the same thing only now I have this file and I have to pull all those values from this range that we defined so I'm gonna go here then first I'll have to use well let's name it I'm gonna start my import range function to make sure I give access to that spreadsheet and we need spreadsheet URL so that's gonna be well I'll just copy the whole URL paste it here you can just do ID of the spreadsheet then that's what I usually do so to simplify this I'll remove this part after this D slash you could leave the whole thing in and after this slash I'm gonna remove that to simplify this that's gonna be a spreadsheet URL comma and in that spreadsheet we want to refer to a range so again in codes I'm going to refer to that range and the range is called products close this and see we need to allow access now that we did this months you have to run this import range function once to just give access you didn't have to do products to give access because if you have a large table you probably don't want to do this so in the beginning you can just do something like a 1 or something like that and give that access so it works and then you're free to do whatever you're supposed to do after that so in my case this is supposed to be products so I'm gonna just go ahead and cut that import range function because I'm gonna need to use it and I'm gonna start with vlookup and then close this so lookup value is gonna be this comma and then we have the table which is where I'm gonna paste my import range function comma the column index is 3 , and this is 0 it is not sorted close hit enter and then just click on this double click to copy this down and that's our view look up from the other tab so those are some examples of using named ranges again if you decide you want to change those or delete those or do something with them make sure you're gonna have to go under your data tab and you're gonna go under named ranges they are all gonna be here if you want to change something or delete just click edit and change it and that will be it for this video thanks for watching please subscribe and I'll see you in the next video

Channel: Learn Google Spreadsheets

Views: 72,865

Keywords: Google Sheets, Named Ranges, dynamic, IMPORTRANGE, Other Sheets, tabs, Arrays, how to, Tutorial

Id: lbp4q8fnom0

Length: 20min 18sec (1218 seconds)

Published: Mon Apr 02 2018