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