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

Mike_Newham

New Member
Joined
Feb 7, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,707
Office Version
  1. 365
Platform
  1. Windows
If you have spaces in the sheet name you need to wrap it in ' like
Excel Formula:
=HYPERLINK("#'Change Request "& B11 & "'!A1","LINK")
 
Solution

Mike_Newham

New Member
Joined
Feb 7, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
If you have spaces in the sheet name you need to wrap it in ' like
Excel Formula:
=HYPERLINK("#'Change Request "& B11 & "'!A1","LINK")
Thanks so much, thats fixed my problems!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,707
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

mctown

New Member
Joined
Feb 24, 2022
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
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
ABC
2Type sheet name here ->sheet1sheet1
3
4Sheets Names
5sheet1active
6sheet2hidden
main
Cell Formulas
RangeFormula
C2C2= HYPERLINK("#" &B2& "!A3",B2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:C2Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
B2List=#REF!#
[/B][/SIZE]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,707
Office Version
  1. 365
Platform
  1. Windows
Please start your own thread for this, as it's significantly different to the original question. Thanks
 

Forum statistics

Threads
1,176,103
Messages
5,901,398
Members
434,890
Latest member
creativimama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top