Hyperlink to sheet and cell

[cedricmeier]

Hello All,

I have 2 columns. Each row contains the name of a sheet in column S and the row of the sheet in column T. I would like to create a link, in column U, on each row, that displays a hyperlink to the sheet and row based on what is located in columns s&t. Is this possible?

Thanks so much,
Cedric

[Tony Valko]

Can you post a few examples and explain what they mean/what should happen?

[cedricmeier]

e.g.


S T U

1 Sheet1 34 (hyperlink - goes to sheet stated in S and row stated in T)

2 Sheet2 5 (hyperlink - goes to sheet stated in S and row stated in T)

3 Sheet3 55 (hyperlink - goes to sheet stated in S and row stated in T)

[cedricmeier]

sorry formatting was bad above...see below


e.g.


--------------S---------------T---------------- U

1---------Sheet1----------- 34---------------(hyperlink - goes to sheet stated in S and row stated in T (Sheet1 - Row 34))

2---------Sheet2------------5----------------(hyperlink - goes to sheet stated in S and row stated in T (Sheet2 - Row 5))

3---------Sheet3------------55-------------- (hyperlink - goes to sheet stated in S and row stated in T (Sheet3 - Row 55)

[Tony Valko]

Try this...

S1 = Sheet1
T1 = 34

This formula entered in U1:

=HYPERLINK("#"&S1&"!A"&T1,"GO")

That will take you to cell A34 on Sheet1.

[cedricmeier]

It keeps saying "reference is not valid". I check it and it looks right. Any idea on what it could be?

I even tried a more basic formula like: =HYPERLINK("#MyRange","Jump There") ....and it still gave the same error.

[cedricmeier]

Very strange...I created a new workbook that contains only your code and 2 sheets and there it works, but for some reason it doesnt work on the workbook that i need it in. The only difference I can see if that the workbook that I need it in has an extension of xlsm whereas the new workbook that i created has an extension of xlsx.

[cedricmeier]

I tried a few different things and it seems to only work when the name of the sheet doesn't have a "-" or "space" in it. Is there any way around this as I have already created tons of code using the current sheet names.

[JessK]

=HYPERLINK("#TabName!cellref","whatyouwantthelinktobecalled")

[Tony Valko]

I tried a few different things and it seems to only work when the name of the sheet doesn't have a "-" or "space" in it. Is there any way around this as I have already created tons of code using the current sheet names.

If the sheet names contain spaces:

S1: Sheet 1
T1: 34

Formula entered in U1:

=HYPERLINK("#'"&S1&"'!A"&T1,"GO")