Multiple Worksheets help

[Pilgrimav8r]

I'm not doing very well here so any help at all is greatly appreciated.

I don't want to use a macro.

I have a single spreadsheet which has multiple worksheets in it 100+. On about worksheet 7, I have put in an index (titles and worksheet number).

All I'm trying to do is click on the index cell on the index worksheet and get it to direct me straight to the corresponding worksheet.

Many thanks in anticipation

 

[RonB1111]

See this video:
http://www.youtube.com/watch?v=9Pu5QSuGcwU

 

[T. Valko]

I'm not doing very well here so any help at all is greatly appreciated.

I don't want to use a macro.

I have a single spreadsheet which has multiple worksheets in it 100+. On about worksheet 7, I have put in an index (titles and worksheet number).

All I'm trying to do is click on the index cell on the index worksheet and get it to direct me straight to the corresponding worksheet.

Many thanks in anticipation

You could use a hyperlink formula.

Do the index cells contain the sheet names? Can you post a few examples?

 

[Pilgrimav8r]

Hi Biff,

Sorry that's way above my expertise I can do all the simple things like add and subtract but that's my limit really. The answer to your question is no they just have a page number, ie 01, 02 but the worksheets do have their own unique name.

Steve

Ps I'm trying not to use an hyperlink as well if I can help it. Sorry I'm not helping much

 

[mark5767]

I set up indexes like this for workbooks with 100+ worksheets using hyperlink formulas as has been suggested. The link is simply the name of each worksheet. If there are spaces in the name you need to add some single quotes. These links were super easy to use and a real blessing... all thanks to the MrExcel forum!

Kind of looked like this...

<TABLE style="WIDTH: 206pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=274><COLGROUP><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow; WIDTH: 43pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 width=57>SKU</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; WIDTH: 67pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=89>Hyperlink</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Formula</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>601</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>601</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>=HYPERLINK("#"&A2&"!B5",A2)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>602</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>602</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>=HYPERLINK("#"&A3&"!B5",A3)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>603</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>603</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>=HYPERLINK("#"&A4&"!B5",A4)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>604</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>604</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>=HYPERLINK("#"&A5&"!B5",A5)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=17>605</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>605</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>=HYPERLINK("#"&A6&"!B5",A6)</TD></TR></TBODY></TABLE>

 

[T. Valko]

Hi Biff,

Sorry that's way above my expertise I can do all the simple things like add and subtract but that's my limit really. The answer to your question is no they just have a page number, ie 01, 02 but the worksheets do have their own unique name.

Steve

If the cell contains 01 and you click on it where should it take you?

 

[Pilgrimav8r]

Am I right in thinking you have to be connected to the web for an hyperlink to work?

 

[T. Valko]

Am I right in thinking you have to be connected to the web for an hyperlink to work?

You can use hyperlinks within files to take you to different sheets or different cells on the same sheet.

Try this experiment...

Open a new file that still has the default sheet names Sheet1, Sheet2, Sheet3 etc.

Enter this sheet name in cell A2: Sheet2

Enter this formula in cell B2:

=HYPERLINK("#Sheet2!A1",A2)

Now, click on cell B2.

You're taken to Sheet2 cell A1.

 

[Pilgrimav8r]

Thanks biff but does that mean you need to be connected to the internet to make the links work?

Surely there is a VBA code that tells you to go to a different sheet which is all I'm after......... I think

Steve

 

[Pilgrimav8r]

Try this experiment...

Open a new file that still has the default sheet names Sheet1, Sheet2, Sheet3 etc.

Enter this sheet name in cell A2: Sheet2

Enter this formula in cell B2:

=HYPERLINK("#Sheet2!A1",A2)

Now, click on cell B2.

You're taken to Sheet2 cell A1.[/QUOTE]

Biff I tried that and it did not work - perhaps I should tell you its Win 7 and excel 2007?

Steve