Hyperlink to a sheet through a cell containing that sheetname

**consultantguy** · 11-16-2007, 04:48 AM

I have a dynamic list of sheets and i would like to be able to click on an name and arrive on that sheet. I have actually worked out the biggest part of the problem, all that remains is finding a clever way to replace the path to the file with some kind of reference, perhaps something like CELL("filenamne"), so that it won't matter if i change the name of the workbook.

Bookname is an array containing all the sheetnames in the workbook.

HYPERLINK("[C:\xxx\Filename.xls]'"&(INDEX(Bookname;ROW()+2)&"'!$A$1");INDEX(Bookname;ROW()+2)))

If anyone could help me I would be sooo happy. I can a link on how to make the list of sheet names if someones interested

/Bjorn

**Gary Brown** · 11-16-2007, 08:17 AM

Try this:-

HYPERLINK(MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1),FIND("]",CELL("filename",$A$1),1)-FIND("[",CELL("filename",$A$1),1)+1)&"&(INDEX(Bookname;ROW()+2)&"'!$A$1");INDEX(Bookna me;ROW()+2)))

**consultantguy** · 11-16-2007, 08:54 AM

YEEEES! Finally! Thank you so much!

There was some small issue about some ' "" & etc.

I'm a bit stressed so I haven't properly translated it back but you can probably understand anyway, the ";" are ","

Not working:

&"'"&(INDEX(Boknamn;ROW()+2)&"'!$A$1");INDEX(Boknamn;ROW()+2)))

Working:

&"&(INDEX(Boknamn;ROW()+2)&"'!$A$1");INDEX(Boknamn;ROW()+2)))

A spent two hours last night on this, feel grat now!

**jespvik** · 05-26-2009, 08:05 AM

Hello,

This is exactly what I am trying to do with one of my workbooks. Can someone please help me with how to set up this for my workbook?? Any help would be great! I have attached a file for reference! Thanks so much!!!

Jillian