WEBVTT

1
00:00:06.666 --> 00:00:08.266
This video will show you how

2
00:00:08.266 --> 00:00:11.666
to create an index sheet
for your Excel workbook.

3
00:00:12.866 --> 00:00:15.666
I have an Excel workbook
that I'm beginning to build

4
00:00:15.666 --> 00:00:18.266
and it currently has four sheets.

5
00:00:18.266 --> 00:00:22.666
In the last session I renamed them
index, Dragon sheet and summary,

6
00:00:22.866 --> 00:00:25.266
and since then I've added a fourth
one, action plan.

7
00:00:26.466 --> 00:00:29.466
I'm going to use this first sheet, index,

8
00:00:29.933 --> 00:00:34.066
to guide users to the other sheets
and to provide a bit of a summary.

9
00:00:34.933 --> 00:00:37.733
So in this video,
I'm going to create the links

10
00:00:38.066 --> 00:00:41.200
to the other worksheets
so that people can access those rather

11
00:00:41.200 --> 00:00:44.000
like a table of contents.

12
00:00:44.200 --> 00:00:48.133
Cell A1 is where I'm going
to put my summary of the workbook

13
00:00:48.133 --> 00:00:51.266
and some instructions.
We'll do that in the next video.

14
00:00:52.466 --> 00:00:56.333
For now, I'm going to create
internal links to the other sheets

15
00:00:56.333 --> 00:01:01.133
in my spreadsheet. So I'm going to do that
in one of several ways.

16
00:01:01.333 --> 00:01:05.266
I'm going to right
click on the cell and select Link.

17
00:01:08.066 --> 00:01:10.866
That brings up a dialogue box.

18
00:01:10.866 --> 00:01:13.866
I'm going to choose place in this workbook

19
00:01:15.466 --> 00:01:17.133
and for the first one

20
00:01:17.133 --> 00:01:20.066
I want it to go to Dragon tests.

21
00:01:21.000 --> 00:01:23.133
When I click on that, it

22
00:01:23.133 --> 00:01:26.000
fills in the text
to display in the top box

23
00:01:26.266 --> 00:01:28.466
and it currently says Dragon tests

24
00:01:29.066 --> 00:01:31.666
and then points to cell A1.

25
00:01:31.666 --> 00:01:33.800
I don't want the cell A1 bit there

26
00:01:33.800 --> 00:01:37.933
and I don't really want the quotes.

27
00:01:37.933 --> 00:01:40.466
I just want Dragon tests.

28
00:01:40.466 --> 00:01:42.466
The cell reference that's in
the second box

29
00:01:42.466 --> 00:01:45.800
is the cell on that worksheet
that it will go to.

30
00:01:45.800 --> 00:01:49.733
By default, this is always cell
A1 and that's where I want it to go.

31
00:01:50.400 --> 00:01:52.133
I'll click okay.

32
00:01:53.000 --> 00:01:54.333
And in cell

33
00:01:54.333 --> 00:01:57.800
A2, I now have a link to Dragon tests,

34
00:01:57.800 --> 00:02:00.600
and that will take me to the Dragon test sheet.

35
00:02:01.000 --> 00:02:02.733
I'm going to do the same for the next one.

36
00:02:04.533 --> 00:02:06.800
So I'm going to click in cell A3,

37
00:02:08.466 --> 00:02:11.333
and I'm going to go to the insert menu

38
00:02:11.333 --> 00:02:15.000
and I'm going to insert a link.

39
00:02:15.666 --> 00:02:17.933
Again, I'm going to a place in this document.

40
00:02:17.933 --> 00:02:20.733
This time I'm going to summary

41
00:02:20.866 --> 00:02:24.733
and again, I'm going to delete the
information from the text to display, that

42
00:02:24.733 --> 00:02:28.133
I don't want to see and click okay.

43
00:02:28.800 --> 00:02:31.000
This time I'm going to use the keyboard.

44
00:02:31.000 --> 00:02:35.200
So first of all, I'm going to go down
from the, from cell A3 into cell A4.

45
00:02:36.333 --> 00:02:39.733
I'm going to use the alt key
to bring up a list of menus

46
00:02:40.800 --> 00:02:44.600
and N for insert.

47
00:02:44.600 --> 00:02:46.933
I and 2 will take me to link,

48
00:02:48.000 --> 00:02:50.466
and then I for link.

49
00:02:51.266 --> 00:02:54.200
By default,
I'm in the type the cell reference field.

50
00:02:55.133 --> 00:02:58.666
I'm going to hit the tab key
to go to select a place in this document

51
00:02:58.933 --> 00:03:03.733
and then use my arrow keys
to go down to action plan.

52
00:03:04.000 --> 00:03:07.666
I'm going to hit the tab key again
and that will take me to text to display.

53
00:03:08.333 --> 00:03:13.533
I'm going to hit the end key and then use
my backspace button and the arrow keys

54
00:03:14.800 --> 00:03:16.800
to navigate around there and delete
the information

55
00:03:16.800 --> 00:03:20.133
I don't want.

56
00:03:20.133 --> 00:03:21.266
That's now correct.

57
00:03:21.266 --> 00:03:25.200
I'm going to use the tab key again,
once, twice to get to

58
00:03:25.200 --> 00:03:27.600
Okay and then hit enter.

59
00:03:27.600 --> 00:03:31.466
And now I have that action plan
in cell A4.

60
00:03:32.333 --> 00:03:35.066
My final task is to make those cells
a little bit bigger.

61
00:03:35.400 --> 00:03:36.733
We will be doing that again

62
00:03:36.733 --> 00:03:40.000
in the next session
when we put the information in cell A1,

63
00:03:40.400 --> 00:03:43.800
but for now, I'm going to right click
and go to column width

64
00:03:45.000 --> 00:03:47.933
and I don't need to make it much bigger,

65
00:03:47.933 --> 00:03:51.266
so I'm going to make it
say 15 and hit enter.

66
00:03:52.266 --> 00:03:54.533
That's it. So now I've got an index sheet.

67
00:03:54.866 --> 00:03:57.266
The summary link is in a different colour
because I visited it.

68
00:03:57.800 --> 00:03:59.800
So I've got a link to Dragon tests,

69
00:04:00.533 --> 00:04:04.466
and that takes me to cell
A1 on Dragon tests, to summary

70
00:04:05.666 --> 00:04:08.466
and that takes me to my summary sheet,

71
00:04:08.466 --> 00:04:10.800
and to action plan

72
00:04:10.800 --> 00:04:12.933
and that takes me to action plan.

73
00:04:12.933 --> 00:04:16.333
So that's how you create an index
sheet for your workbook.