Hi,
I am wanting to create a list of sheet names in a workbook on the first
sheet, and then assign each name in the list a hyperlink to go to that
particular sheet. Does anybody know a formula that I can use to set this up?
Thanks,
Jane
Hi,
I am wanting to create a list of sheet names in a workbook on the first
sheet, and then assign each name in the list a hyperlink to go to that
particular sheet. Does anybody know a formula that I can use to set this up?
Thanks,
Jane
Hi
Create an UDF (Activate VBA editor pressing Alt+F11, insert a new module
when there is no one in your workbook, and copy the code below into it).
Public Function TabI(TabIndex As Integer, Optional parVolatile As Date) As
String
TabI = Sheets(TabIndex).Name
End Function
On sheet you want to have the list of sheets in, into cell A1 enter the
formula
=IF(ISERROR(TABI(ROW(),NOW())),"",TABI(ROW()))
, and copy it down for some number of rows.
In case you want a heading for sheets list in cell A1, enter the modified
formula into cell A2
=IF(ISERROR(TABI(ROW()-1,NOW())),"",TABI(ROW()-1))
, and again copy it down.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )
"JaneC" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am wanting to create a list of sheet names in a workbook on the first
> sheet, and then assign each name in the list a hyperlink to go to that
> particular sheet. Does anybody know a formula that I can use to set this
> up?
>
> Thanks,
>
> Jane
Hi Jane,
See recent post for interesting discussion about this formula...
how to obtain sheet name?
Ken Johnson
I was hoping that would be a link.
Maybe this time..
how to obtain sheet name?
Ken Johnson
I give in!
Ken Johnson
"JaneC" wrote:
> I am wanting to create a list of sheet names in a workbook on the first
> sheet, and then assign each name in the list a hyperlink to go to that
> particular sheet. Does anybody know a formula that I can use to set this up?
You're looking to build a table of contents, I believe ..
One good way is Jim Cone's fine, free product (his XL Extras add-in) at his:
http://www.realezsites.com/bus/primi...e/products.php
Scroll right down to the bottom of the page for the download link:
XL Extras - release 1.19 ... Download
A collection of additional features including...
**create table of contents with hyperlinks to each sheet
Jim's add-in will do exactly what you're looking for (and much more ..)
After installing, just click Insert > Table of Contents, then sit back
and watch his magic go to work <g> !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ken Johnson" wrote:
> I give in!
Believe you were referring to this thread:
http://tinyurl.com/mzhef
... but I'm not sure that's what the OP is looking for <g>
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
> After installing, just click Insert > Table of Contents
The "Table of Contents" is a new menu item
which appears under the "Insert" dropdown
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Hello,
Another approach without VBA:
http://www.sulprobil.com/html/get_cell.html
Look at the proposed name WorksheetName and extract characters right
from "]".
HTH,
Bernd
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks