Table of contents/index

P

Peter Barker

I have a long spread sheet which has a number of specific sections to it.
Does anyone know whether it's possible to set a 'table of contents' at the
top of the page so that I can click on one of the titles and it will jump
down to that section. At the moment I have to keep scrolling down the page
which takes forever. Many thanks for any help anyone can provide.
 
G

Gary''s Student

Hi Peter:

Without VBA, we can create a jump table as follows:

1. In a spare area, say G1 thru H4, enter the table:

Input section A1000
calculations A2000
plotting A3000
reports A4000

the table has both titles and locations.

2. In cell A1 put a Data Validation drop-down list to pull in items from G1
thru G4

3. In A2 enter:

=HYPERLINK("[Book2]Sheet1!" & VLOOKUP(A1,G1:H4,2,FALSE),A1)

This example assumes that the workbook is Book2 and the sheet is Sheet1
 
D

David McRitchie

Hi Peter, and Bob,
Except for usage of hyperlinks there's not much on my
BuildTOC page specific to your request as the main/original
focus is to create a Table of Contents of sheetnames, not
areas of the same sheet. Though it does cover hyperlinks
as well.

All you indicate is that you have a large worksheet, is the
worksheet generated by something else or is it just a big
list that you keep adding to manually.

If your data is Grouped Totals you can change the summary
level that is visible, and review the major headings and
select one then show everything.

If you are trying to get to a particular point in an
alphabetical listing such as a phone book, no index
needed you can the FindFirstChar macro at end of
http://www.mvps.org/dmcritchie/excel/code/proper.txt

To build a table of contents for one sheet would be easiest
to create the index on another worksheet. You could
combine things later if the main part just consists of
values and not formulas or links.

To simply build your own Table of Contents manually you
can use
1) Object Hyperlinks (Ctrl+K)
2) Worksheet HYPERLINK Formula within same sheet
=HYPERLINK("#"&CELL("address",C5),C5)

If you are going to sort your data, after creating
hyperlinks you are going to have a problem.

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bob Phillips" wrote ...
 
G

Gord Dibben

You can name the top cell in each range then pick that name from the namebox to
jump.

Insert>Name>Define


Gord Dibben MS Excel MVP
 
Top