If you're using Google Sheets and, at the bottom, you have several different
sheets that you're working with, you may find that you want to create a list of
them. Say, if you had a table of contents, which is what I'm doing here,
you could automatically list out the names of the sheets and you probably
want these names to change if you update the names of the sheets. That's what
we're going to go over here and I have several different ways to do it here
depending on what you want to do. Let's go into cell B3 and look. This
is a custom function and we're going to create this as we go along. We call it
SheetName and it's referencing this cell. We'll talk about why it's doing
that in a minute, but first we're going to talk about how to create a custom
function. It's only a couple steps. What you want to do is you want to; go to
tools and use the script editor. When you left click on script editor, it's
going to launch a new tab. Copy these from my website. It's linked from
this card in the upper right-hand corner. Or, you can just go to the web address
that I'll list below in the video description.
Don't try to type them out yourself. When you put them in here, you need to
save this. I just called it code.gs. It doesn't really matter what you call it.
I'll click Save. Just briefly, we have SheetName that we created we're going
to go over that first. That's going to just return the name of the sheet that
the function is on. This function is going to return the names of all the
sheets, and this function is going to return the name of a particular sheet
that you specify. That's three of them. You'll notice there's four them here
because this last one is not a custom function. But the first one, so SheetName.
We just created this function. Let's type it in. It doesn't pre-populate with the
little helper text to tell you the different functions because this one is not made
by Google Sheets, so it doesn't know what you're doing. Just do sheet name and
we pass it nothing inside the parenthesis. You need to parentheses
regardless. Hit enter. It takes a while to think
about it, but then it returns the sheet name Table of contents. If you do
SheetNames, we don't need the C15 in here; we'll add that in a bit. Press ENTER.
It thinks about a little bit and it returns the names of the worksheets. But,
it returns all of them. So if I had 15, it would spit out a list of 15. Then the
last one that we built is called SheetNumber. You pass this the number of
the sheet that you wanted to return. Say we wanted sheet number four, which is
Detail 2, with a space in it. That spaces there on purpose, we'll show you
in a minute. That's probably one, two, three, that's the
fourth sheet and there we go. It returned Detail 2. The issue with these, and
why I'm going to offer fourth alternative, is that if you change the
name of one of the sheets, these don't automatically update. We'll come down
here and we're going to call summary, we're going to call it Lead Sheet instead.
We'll come back, and this one's wrong. It still says Summary. Now, this one isn't
wrong because they didn't change the name Table of contents. But, if I did, it
would not have updated. So keep that in mind as you make these custom functions.
There is a workaround to this. What you can do is, in the name of the
function, you can pass it a range, but don't overlap it with the name of the
sheet. We'll do A15 and we'll make it big, we'll say, over to G55. If you
give it this range, what that's going to do, first of all, since I changed it you
can see that it updated. But, if I didn't change that, that would not have updated.
I'm going to go change the name of Lead sheet back to Summary. We'll see that this
didn't update. But, now that this is referencing this range, if I use this
sheet for anything else in that range, let's say I just put a 5 in, it will
update the function. So that's a decent workaround for having this function
update. It's not going to work all the time though. It's not perfect because, if
you don't put anything in this range, it won't update. If you are using this
range, you were using a lot, it's going to slow
you down from updating all the time. The last function that we have this, is also
in the description, is kind of a convoluted list of built-in functions
that you just copy and paste in here and these will update automatically. It'll
take a bit. They might not update in real time, but they will update as you use the
sheet. I'll show you how this works. It's a long function and the =IF(TODAY()=TODAY()
is just a trick in here to get it to update. Then it's a
regular expression where it extracts the name of the worksheet. But, what you have
to do is fill in is the name of the worksheet here. But, once you fill it in, it will
update. If I call this one Detail1, and I hit enter, it will return Detail1.
But, if I go to it and I call it Detail[space]1, and I come back, it
updated automatically. You'll have to build it first. So you'll have to go in
and reference this one to the Table of contents. You'll have to reference the next one to
Summary. If you have lots of sheets, it'll take you a while to build
it. But, it will be dynamic and you build it once and it'll work. In between these
four, you should be able to find one that works for what you're doing in your
sheet. Once you're done with this, if you want to share your Sheet, but you
want to do it in a secure way, we're going to talk in this next video about how
to do that. Thanks for watching and please subscribe if you liked the video.
Thanks!