An index for spreadsheet with 100 tabs

  • Thread starter Thread starter Belch
  • Start date Start date
B

Belch

I have Excel 97 SR-1. My spreadsheet has over 100 tabs and it is
cumbersome to move around so I want to do the following.

The first tab, named "Index", lists the names of all the other tabs.
On the Index worksheet I want to highlight a cell with the name of
another tab and then click another cell on the Index sheet with the
words "Go To It" and have Excel take me to the tab highlighted.

Also, I want to put on each of the 100 tabs a cell that says "Go To
Index" that will bring me back to the Index worksheet.

How do I do all this? I do not have Visual Basis and if I did I do not
have the time nor the brains to learn it (mainly the latter).

Belch
 
Hi

Create an UDF, p.e.
Public Function TabByIndex(TabIndex As Integer) As String
Application.Volatile
TabByIndex = Sheets(TabIndex).Name
End Function

Save your workbook
On sheet Index, into cell A2 enter the formula
=IF(ISERROR(TabByIndex(ROW(A2))),"",TabByIndex(ROW(A2)))
Into cell B2 enter the formula
=IF(A2="","",HYPERLINK(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))
,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))+1)&A2&"!A1",A2)
)
(I hope I edited all semicolons to commas here!)

Copy A2:B2 down for as much rows you think you'll have sheets
 
Are you aware of the fact that XL has a built in table of contents for
worksheets?

XL provides a drop down (pop-up) menu, which displays all the sheets of a WB
?

Right click on any of the directional arrows that are to the left of the
sheet names.
This gives you a list of about 15 sheet names with the option to expand to
an infinite scrolling list of *all* the sheets in the WB.

This list is in the order that the sheets are in the WB.
Any sorting must be done by manually moving the sheets within the WB itself.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have Excel 97 SR-1. My spreadsheet has over 100 tabs and it is
cumbersome to move around so I want to do the following.

The first tab, named "Index", lists the names of all the other tabs.
On the Index worksheet I want to highlight a cell with the name of
another tab and then click another cell on the Index sheet with the
words "Go To It" and have Excel take me to the tab highlighted.

Also, I want to put on each of the 100 tabs a cell that says "Go To
Index" that will bring me back to the Index worksheet.

How do I do all this? I do not have Visual Basis and if I did I do not
have the time nor the brains to learn it (mainly the latter).

Belch
 
Hi


RagDyeR said:
Are you aware of the fact that XL has a built in table of contents for
worksheets?

XL provides a drop down (pop-up) menu, which displays all the sheets of a WB
?
Click to expand...

In Excel2000 too


Arvi Laanemets
 
Is that a statement or a question Arvi?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Hi


RagDyeR said:
Are you aware of the fact that XL has a built in table of contents for
worksheets?

XL provides a drop down (pop-up) menu, which displays all the sheets of a WB
?
Click to expand...

In Excel2000 too


Arvi Laanemets
 
Belch,

No VBA Coding Required...

My Excel add-in "Excel Extras" adds a "Table of Contents" item to
the Insert Menu that when clicked, automatically adds a new sheet
to the workbook.
The new sheet has a listing of all sheets and the number of printable
pages for each sheet. Each sheet name on the Table of Contents
is hyperlinked to the actual sheet.
Note: you can use the "Web" toolbar arrows to return to the
Table of Contents sheet.

The add-in has additional features including:
Sheet sorting
Print area determination
Header and Footer info - add or clear
Font options
Ability to "Flip" selections (reverse data order).

The add-in is available - free - upon direct request.
Comes with Word.doc install/use instructions.
(remove xxx from my email address)

Regards,
Jim Cone
San Francisco, CA
(e-mail address removed)
 
RD

Wow. Thanks! This will do fine until I can figure out how to do it in
VBA. Big improvment. And it works with Excel 97 SR-1.

Belch
 
I said the same thing (Wow!) the first time I learned about it from these
NGs, a long time ago.

It's very well hidden in the Help files.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD

Wow. Thanks! This will do fine until I can figure out how to do it in
VBA. Big improvment. And it works with Excel 97 SR-1.

Belch

-----

Are you aware of the fact that XL has a built in table of contents for
worksheets?

XL provides a drop down (pop-up) menu, which displays all the sheets of a WB
?

Right click on any of the directional arrows that are to the left of the
sheet names.
This gives you a list of about 15 sheet names with the option to expand to
an infinite scrolling list of *all* the sheets in the WB.

This list is in the order that the sheets are in the WB.
Any sorting must be done by manually moving the sheets within the WB
Click to expand...
itself.
 
Hi Jim.

It’s dynamite. Some comments.

Sorting Sheets is exactly what I want.

Header and Footer info - add or clear - terrific because I can quickly
add Headers/Footers to all Selected sheets at one time instead of
individually.

Table of Contents - I found that if a new sheet is added it does not
add to the "Table of Contents" but by doing Insert/Table of Contents/
Replace existing TOC/Yes the new sheets are added in. Ditto where the
name of an existing sheet is changed.

Thank you.

Belch

-----
 
Back
Top