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

✔ 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! ErrorDon'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

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

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.

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

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

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 ?

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

but =HYPERLINK("#"&"1!A1",A2) is working. Here 1 is sheet name & A2 contains 1 itself.

but =HYPERLINK("#"&A1&"!A2",A2) is not even showing 1 in B2 rather it is showing #Value! Error

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! ErrorDon'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

Oh yeah actually the workbook was corrupted ! it is done now. :-)

Thank you very much

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.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History