Hyperlink via indirect cell reference
 

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

I won't hold it against you - however as I have to do this many times I am
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

Well..., after another thought and assuming all your sheets begin with
"Sheet" and with an sequential number beginning with 1 - try this:
In cell A1 - in the other spare sheet - put this formula and copy down as
much as your sheets count:
=HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW ()&"!A1")),"Go to
Sheet"&ROW())
Micky


"BabyMc" wrote:

I won't hold it against you - however as I have to do this many times I am
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

=HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)


"מיכאל (מיקי) אבידן" skrev:

Well..., after another thought and assuming all your sheets begin with
"Sheet" and with an sequential number beginning with 1 - try this:
In cell A1 - in the other spare sheet - put this formula and copy down as
much as your sheets count:
=HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW ()&"!A1")),"Go to
Sheet"&ROW())
Micky


"BabyMc" wrote:

I won't hold it against you - however as I have to do this many times I am
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

Of course !
My apology for not noticing the existence of the sheets name list in col. A
However - your formula can be shorter:
=HYPERLINK("#"&A1&"!A1","Jump to Sheet: "&A1)
Micky



"excelent" wrote:

=HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)


"מיכאל (מיקי) אבידן" skrev:

Well..., after another thought and assuming all your sheets begin with
"Sheet" and with an sequential number beginning with 1 - try this:
In cell A1 - in the other spare sheet - put this formula and copy down as
much as your sheets count:
=HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW ()&"!A1")),"Go to
Sheet"&ROW())
Micky


"BabyMc" wrote:

I won't hold it against you - however as I have to do this many times I am
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

Thanks for both of your replies. I will try them out following the festive
break.

Before I do, though, might I trouble to ask that - as my sheets aren't
actually called Sheet1 etc (they are actually various 6 digit numbers) -
would this formula still work?

Thanks once again.

"excelent" wrote:

=HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)


"מיכאל (מיקי) אבידן" skrev:

Well..., after another thought and assuming all your sheets begin with
"Sheet" and with an sequential number beginning with 1 - try this:
In cell A1 - in the other spare sheet - put this formula and copy down as
much as your sheets count:
=HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW ()&"!A1")),"Go to
Sheet"&ROW())
Micky


"BabyMc" wrote:

I won't hold it against you - however as I have to do this many times I am
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

Weather you have all sheets name in column A starting cell A1 - you might
consider Excelents formula and/or mine [the second].
Micky


"BabyMc" wrote:

Thanks for both of your replies. I will try them out following the festive
break.

Before I do, though, might I trouble to ask that - as my sheets aren't
actually called Sheet1 etc (they are actually various 6 digit numbers) -
would this formula still work?

Thanks once again.

"excelent" wrote:

=HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)


"מיכאל (מיקי) אבידן" skrev:

Well..., after another thought and assuming all your sheets begin with
"Sheet" and with an sequential number beginning with 1 - try this:
In cell A1 - in the other spare sheet - put this formula and copy down as
much as your sheets count:
=HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW ()&"!A1")),"Go to
Sheet"&ROW())
Micky


"BabyMc" wrote:

I won't hold it against you - however as I have to do this many times I am
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.

Hyperlink via indirect cell reference
 

I manged to find a little bit of time to take your formula(s), paste in to my
workbook and amend it to suit my specific requirements.

It seems to work - brilliant!!!

Thanks both.

"מיכאל (מיקי) אבידן" wrote:

Of course !
My apology for not noticing the existence of the sheets name list in col. A
However - your formula can be shorter:
=HYPERLINK("#"&A1&"!A1","Jump to Sheet: "&A1)
Micky



"excelent" wrote:

=HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)


"מיכאל (מיקי) אבידן" skrev:

Well..., after another thought and assuming all your sheets begin with
"Sheet" and with an sequential number beginning with 1 - try this:
In cell A1 - in the other spare sheet - put this formula and copy down as
much as your sheets count:
=HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW ()&"!A1")),"Go to
Sheet"&ROW())
Micky


"BabyMc" wrote:

I won't hold it against you - however as I have to do this many times I am
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

Will you hold it against me if I dare to suggest that you RIGHT(!) click one
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros.
Micky



"BabyMc" wrote:

Hi

I have workbook that contains a number of sheets. On a separate sheet I
would like to be able to insert a hyperlink so that I can jump to a specific
sheet.

However, rather than inserting all of the hyperlinks manually (I will have
to replicate this over many workbooks) I wondered if there was a formula to
allow me to jump to a cell (say A1) in another worksheet, based on the name
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3",
"Sheet4".

In another sheet I would have

A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.