Solved hyperlink a sheet within same workbook

October 7, 2012 at 23:19:22
Specs: Windows Vista
hello friends
I have a workbook conting 100 worksheets named 1 to 100 & a summary sheet.
I would like to open the specific sheet as i click on a hyperlink
Eg I have a column containg 1,2,3 ,..........100.
I wish to open the sheet "1" by clicking on 1 written in column a of the summary sheet
I have done this seperately for specific sheet by using Hyperlink formula =HYPERLINK("#"&"1!A1","1")
but as these sheets are going to increase its clumsy to write the same formula again & again
Can I put the formula once in summary sheet on cell containg "1" & drag this to entire column so that whenever I add a sheet I need not to write the hyperlink formula again.
I am using Excal 2003
Please sort out my problem
I'll be thankful to u

See More: hyperlink a sheet within same workbook

Report •

✔ Best Answer
October 11, 2012 at 03:42:03
You have both cells A1 & A2 in your formulas as posted.

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

In the above formula, the jump to sheet number, must be in cell A1,
and it will link you to Sheet Number, Cell A2.
I have no idea what is in cell A2, so I don't know what it
will display as a friendly name.

B2 rather it is showing #Value! Error

Don't know what your doing in cell B2.

Have no idea why you would be getting a #VALUE error:

In Microsoft Excel, when you perform a mathematical operation on cells that contain text and values, you may receive a #VALUE! error.

What else is going on, are you calculating the sheet number?
Are the cells formatted as TEXT?

Open a brand new workbook and try it on a clean sheet, perhaps you have
a corrupted worksheet.

In cell A1 enter the sheet number you wish to jump to.

In cell B1 use one of the formulas.


MIKE

http://www.skeptic.com/



#1
October 8, 2012 at 07:30:40
I have a column containg 1,2,3 ,..........100.

You don't need an entire column, just one cell.

In cell A1 enter the sheet number you wish to jump to.

In cell B1 use one of the following formulas.

If the Sheet names DO NOT have a space, IE Sheet2
use this formula:

=HYPERLINK("#"&"Sheet"&A1&"!A1","Jump To Sheet # "&A1)

If the Sheet DOES have a space, IE Sheet 2
use this formula:

=HYPERLINK("#"&"'Sheet "&A1&"'!A1","Jump To Sheet # "&A1)

If your sheet names are ONLY numbers, IE 2
use this formula:

=HYPERLINK("#"&A1&"!A1","Jump To Sheet # "&A1)


See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
October 8, 2012 at 09:10:38
You can also use a macro to create a Table Of Contents sheet with links to each sheet.

This site is just one of the many hits you'll get if you DAGS for Excel TOC.

http://blogs.office.com/b/microsoft...

I copied the code from that page into a workbook and it ran flawlessly the first time I tried it. It also ran flawlessly when I added sheets to the workbook and ran it again. Each time you run it it deletes the old TOC and recreates a new one based on the current sheets in the workbook.

Even though it worked fine for me, if I were you I'd test it in a back-up copy of your workbook just in case things don't work out as well as they did for me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
October 9, 2012 at 04:51:54
thanks mike but =HYPERLINK("#"&A1&"!A1","Jump To Sheet # "&A1)

is not working for me.
yeah I hv sheets name only no. i.e. 1,2,3.......

I am using =HYPERLINK("#"&"1!A1",A2) to hperlink sheet "1 ",
and its working properly bt i dont want to write the formula again & again for 100 sheets.
I


Report •

Related Solutions

#4
October 9, 2012 at 05:51:20
The first thing that comes to mind is to make sure you have only a number in cell
A1, if you have a hidden space either before or after the number, it will not work.

EDIT ADDED:

Also make sure your Sheet names are only a number with no spaces.


MIKE

http://www.skeptic.com/


Report •

#5
October 9, 2012 at 21:28:59
Yes Mike both sheet name as well as A1 have number with no spaces
yet =HYPERLINK("#"&A1&"!A1","Jump To Sheet # "&A1) is not working even giving ##### in place of Friendly Name
should I use A1 as number or general ?

Report •

#6
October 10, 2012 at 06:19:08
Something more is going on here, you should not be getting the hash marks.

The hash marks usually mean a number is too wide for the cell,
or the formula in the cell has produced a result that is too wide to display.

EDIT ADDED:

At worst you should get a Reference Not Valid error message.

MIKE

http://www.skeptic.com/


Report •

#7
October 10, 2012 at 21:30:22
but =HYPERLINK("#"&"1!A1",A2) is working. Here 1 is sheet name & A2 contains 1 itself.

Report •

#8
October 10, 2012 at 21:42:58
but =HYPERLINK("#"&A1&"!A2",A2) is not even showing 1 in B2 rather it is showing #Value! Error

Report •

#9
October 11, 2012 at 03:42:03
✔ Best Answer
You have both cells A1 & A2 in your formulas as posted.

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

In the above formula, the jump to sheet number, must be in cell A1,
and it will link you to Sheet Number, Cell A2.
I have no idea what is in cell A2, so I don't know what it
will display as a friendly name.

B2 rather it is showing #Value! Error

Don't know what your doing in cell B2.

Have no idea why you would be getting a #VALUE error:

In Microsoft Excel, when you perform a mathematical operation on cells that contain text and values, you may receive a #VALUE! error.

What else is going on, are you calculating the sheet number?
Are the cells formatted as TEXT?

Open a brand new workbook and try it on a clean sheet, perhaps you have
a corrupted worksheet.

In cell A1 enter the sheet number you wish to jump to.

In cell B1 use one of the formulas.


MIKE

http://www.skeptic.com/


Report •

#10
October 11, 2012 at 04:13:16
Oh yeah actually the workbook was corrupted ! it is done now. :-)
Thank you very much

Report •

#11
October 11, 2012 at 10:35:20
For what it's worth, you can save yourself the trouble of clicking the link with a Worksheet_Change macro.

Right click on the sheet tab where you are using the HYPERLINK function, choose View Code and paste in the following code. Enter a number in A1 and press enter.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$1" Then
      If Target > Sheets.Count Then
         MsgBox "That sheet does not exist, please try again."
      Else: Sheets(Range("A1")).Activate
      End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question