Dynamic Hyperlinking to freshly copied sheets e.g. "Sheet1 (2)"

[mcook36155]

Hello

I have tried using the hyperlink formula to dynamically link to newly created test sheets from a template sheet. Its a basic workbook at this point with the template having name "sheet1", and the subsequent test sheets having names "sheet1 (2)" and so on.

I have used the formula =HYPERLINK(CONCAT("#",B10,"!A1"),D10) where cell B10 contains the sheet name "sheet1 (2)", and cell D10 just contains the test number "1". when I try to use the link it gives an error box saying "Reference isn't valid."

But when I do the exact same referencing cell B9 containing the sheet name "sheet1" it works

is there a way to make it work for these copied sheets?

 

[dreid1011]

Because the sheet name "Sheet1 (2)" has a space in it. Try this:

=HYPERLINK(CONCAT("#'",B10,"'!A1"),D10)

Added single quotes to surround the sheet name.

 

[mcook36155]

Thankyou, this has solved it!!

Not that i understand why it is required... if the spaces are part of the reference cells contents B10 surely it should read that there are spaces, why do we need to tell it it has spaces?

Clearly it is needed tho

 

[dreid1011]

I could pretend to know the answer why it works the way it does, but alas, I do not. Anyway, you're welcome, and happy to help.