Multiple Worksheets help

Pilgrimav8r

New Member
Joined
May 7, 2012
Messages
13
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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>
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,454
Messages
6,101,947
Members
447,765
Latest member
bhutta5437

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top