Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of contents/index
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of contents/index
See http://www.mvps.org/dmcritchie/excel/buildtoc.htm
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Peter Barker" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of contents/index
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 -- Gary's Student gsnu200705 "Peter Barker" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of contents/index
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 ... See http://www.mvps.org/dmcritchie/excel/buildtoc.htm "Peter Barker" wrote... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of contents/index
Examples of manually creating links within worksheet are in
the following topic and the topics immediately before and after. Hyperlink Worksheet References outside of quotes (#hyperlink_ref) http://www.mvps.org/dmcritchie/excel...#hyperlink_ref |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of contents/index
You can name the top cell in each range then pick that name from the namebox to
jump. InsertNameDefine Gord Dibben MS Excel MVP On Sat, 10 Feb 2007 03:56:00 -0800, Peter Barker wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a Word Table from a Excel Macro | Excel Discussion (Misc queries) | |||
How do I link a cell outside a pivot table to one inside the table | Excel Discussion (Misc queries) | |||
Custom field in Pivot Table? | Excel Discussion (Misc queries) | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel |