Dynamic Hyperlink issues (Changing part of the sheet name based on a cell value)

[Mike_Newham]

Hello all,

Im hoping someone will be able to help as i'm a little stumped.

I am typing to create a dynamic hyperlink to a diferent sheet where only part of the sheet name is take from a cell value.

So I have a front sheet called 'Log' and a number of sheets after it called 'Change Request 1', 'Change Request 2', Change Request 3' and so on.

In cell B11 on the front sheet is a number, this could be from 1 to the number of the last Change Request sheet.

I am trying to create a dynamic link that takes the number in B11 and adds it to the end of the words 'Change Request ' and then link to that sheet but i keep getting the following error: 'Refrence isn't valid'

I am just aiming to be taken to sheet, the cell isn't important so i have selected A1.

The fomulas i've tried are as follows:
=HYPERLINK("#Change Request "& B11 & "!A1","LINK")
=HYPERLINK("#Change Request"& " " & B11 & "!A1","LINK")
=HYPERLINK(CONCATENATE("#Change Request ", B11, "!A1"),"LINK")
=HYPERLINK(CONCATENATE("#Change Request"," ", B11, "!A1"),"LINK")
=HYPERLINK(CONCATENATE("#","Change Request ",B11,"!A1"),"LINK")
=HYPERLINK(CONCATENATE("#","Change Request"," ", B11, "!A1"),"LINK")

If I change the name of the 'Change Request 1' to simply '1' and use one of the following:
=HYPERLINK("#" & B11 & "!A1","LINK")
=HYPERLINK(CONCATENATE("#",,B11,"!A1"),"LINK")

Then all works fine, but when I try to combine text with a cell value I get errors. Hopefully I'm doing something daft and this is an easy fix, any advise welcome!

Thanks,
Mike

 

[Fluff]

If you have spaces in the sheet name you need to wrap it in ' like

=HYPERLINK("#'Change Request "& B11 & "'!A1","LINK")

 

[Mike_Newham]

If you have spaces in the sheet name you need to wrap it in ' like

=HYPERLINK("#'Change Request "& B11 & "'!A1","LINK")

Thanks so much, thats fixed my problems!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[mctown]

Hello,

Am new here and I don't know if I should create a new thread but my question is related to dynamic hyperlinks.
Anyways, I created a dynamic hyperlink that can jump to desired sheet based on the sheet name. It works fine. The issue is that the dynamic hyperlink won't work if sheet are hidden.

I have tried the VBA codes on YouTube: Learn Excel from MrExcel - "Hyperlink to a Hidden Worksheet" - Podcast #1729

It only works if sheet hyperlink is created using "Ctrl K" process.

Would appreciate any help.

Thanks in advance!

dynamic-hyperlink.xlsx
	A	B	C
2	Type sheet name here ->	sheet1	sheet1
3
4	Sheets Names
5	sheet1	active
6	sheet2	hidden
main

Cell Formulas
Range	Formula
C2	C2	= HYPERLINK("#" &B2& "!A3",B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C2	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=#REF!#

[/B][/SIZE]

 

[Fluff]

Please start your own thread for this, as it's significantly different to the original question. Thanks

 

[mctown]

Please start your own thread for this, as it's significantly different to the original question. Thanks

Ok, will do. Thanks.