WEBVTT

00:02.180 --> 00:03.060
Okay guys.

00:03.070 --> 00:04.320
And welcome back.

00:04.370 --> 00:09.830
Now it turns out we're not quite done with user defined functions just yet.

00:09.830 --> 00:16.310
I've had a question from a student named Ram who has asked in the what can I have for dinner section

00:16.310 --> 00:18.200
of this course in Level 2.

00:18.270 --> 00:23.320
You remember the one where we we type in the names of all the sheet names down here.

00:23.400 --> 00:25.520
And then we hyperlinked to them.

00:25.580 --> 00:30.770
Is there a formula that we can use that will actually read all of the sheet names.

00:30.770 --> 00:37.380
The simple answer is there isn't but we can write a really simple user defined function to do just that.

00:37.400 --> 00:43.100
So I've tagged this into the downloading you'll may find this at the bottom of the download code in

00:43.100 --> 00:48.860
your get your code here and there what we have is a function called Get she name.

00:48.890 --> 00:51.130
And that should give you a clue as to why it's going to do.

00:51.290 --> 00:58.550
And we're going to we're going to have a number as long now the number is going to be the index number

00:58.640 --> 00:59.720
of the sheet.

00:59.720 --> 01:05.330
So you'll recall that she won his index one the second sheet along.

01:05.330 --> 01:07.940
This is the order in which they appear.

01:07.940 --> 01:11.430
Is she to be sheets too.

01:11.480 --> 01:14.320
And that's basically all the number is going to do.

01:14.660 --> 01:20.420
So you'll recall from an L in the earlier lectures that the name of the function must be where we return

01:20.420 --> 01:21.230
the value to.

01:21.230 --> 01:26.500
So get sheep name equals sheets number and name.

01:26.540 --> 01:28.870
So let us have a look at an operation.

01:28.880 --> 01:35.520
So this is the first sheet in our web book so if I typed get any His air gets sheets name there.

01:36.890 --> 01:42.890
If I click it and then click one that is the first sheet going left to right.

01:43.120 --> 01:44.680
And that and hit enter.

01:44.720 --> 01:48.220
I get sheet one if I change that to sheet 2.

01:49.650 --> 01:53.260
I get get your code here because that's the name second sheet.

01:53.420 --> 02:00.020
But the idea of this is that we have a formula that we can drag down now obviously typing one in here

02:00.020 --> 02:02.790
and two in there is is not going to do the trick.

02:03.200 --> 02:06.560
But what we can do is incorporate the row.

02:06.560 --> 02:15.440
So if I hit one call on one and close the brackets and then hit Enter number one is she won.

02:15.510 --> 02:23.450
Now I can drag this down and row will increase to two and then three and then four and then five and

02:23.450 --> 02:24.590
so on and so on.

02:24.620 --> 02:30.280
So literally if you have a hundred sheets across here you just write this formula once you see in this

02:30.280 --> 02:33.530
you d.f. get sheet name dragged down a hundred times.

02:33.590 --> 02:39.350
And all of your sheet names will be listed in the order in which they appear across here.

02:39.350 --> 02:45.440
Now obviously you can add an error trap at the front here so we could say equals.

02:45.590 --> 02:54.230
If our or open brackets What do we want to return if it's an error lechery 10 nothing hit enter and

02:54.230 --> 03:00.140
then drag that down and then we'll just get blanks where we've run out of sheet names.

03:00.200 --> 03:00.760
Okay.

03:00.920 --> 03:03.510
Now I'll see you in the next section.
