Best Way to Create Sheet Hyperlinks from a List?

[mark5767]

I think I have this working, but it seems a little clunky. Just wondering if there is a simpler way to get this done.

I have a list of product SKUs on a Summary sheet and I want to create hyperlinks for each SKU to go to the SKU cost detail worksheet (each one is simply named with the SKU #).

So it currently looks like this (columns A thru D, row 2 has the formulas)...
A __ B___________ C____________________ D____________
SKU FilePath\Name Concatenate(B2,A2,"!B5") =Hyperlink(C2,A2)

When a customer clicks on a SKU in the summary they go to cell B5 on the SKU cost detail sheet. This seems to work, but again it seems a little awkward.

I believe I need to use the Hyperlink formula because there can be 50 or more SKUs, so right clicking to create hyperlinks is not a realistic option. The filepath\name should always be the active workbook so is there a shorter way to make this work in the hyperlink formula?

Or is there just a better way to create these hyperlinks? Thoughts and comments greatly appreciated, thank you!

 

[jbeaucaire]

The convenience of concatenating strings together to create a link in the Hyperlink() function shouldn't be considered an awkward use.

 

[T. Valko]

I think I have this working, but it seems a little clunky. Just wondering if there is a simpler way to get this done.

I have a list of product SKUs on a Summary sheet and I want to create hyperlinks for each SKU to go to the SKU cost detail worksheet (each one is simply named with the SKU #).

So it currently looks like this (columns A thru D, row 2 has the formulas)...
A __ B___________ C____________________ D____________
SKU FilePath\Name Concatenate(B2,A2,"!B5") =Hyperlink(C2,A2)

When a customer clicks on a SKU in the summary they go to cell B5 on the SKU cost detail sheet. This seems to work, but again it seems a little awkward.

I believe I need to use the Hyperlink formula because there can be 50 or more SKUs, so right clicking to create hyperlinks is not a realistic option. The filepath\name should always be the active workbook so is there a shorter way to make this work in the hyperlink formula?

Or is there just a better way to create these hyperlinks? Thoughts and comments greatly appreciated, thank you!

If the hyperlink is to a sheet in the same file then you don't need to include a FilePath\Name. What is your FilePath\Name?

For example...

A2 = 12345 (a sku that is also a sheet name)

=HYPERLINK("#"&A2&"!B5",A2)

That will produce a hyperlink with the name of 12345 that will take you to 12345!B5 when clicked.

 

[mark5767]

Thanks Biff!

That works very well for what I'm trying to do! The filepath\name is the same file I'm currently using, so it's a long reference to either the hard drive or server depending on where it's currently saved. That was the clumsy part, I was thinking of trying to use the filename formula in lieu of using a text reference to the filepath\name, but your method avoids this step altogether. Nice!

I'm not quite sure I understand exactly how this piece of code operates...

#"&A2&"

But it sure appears to create a sheet name that is recognized as such by the Hyperlink function.

Thanks again!

 

[T. Valko]

Thanks Biff!

That works very well for what I'm trying to do! The filepath\name is the same file I'm currently using, so it's a long reference to either the hard drive or server depending on where it's currently saved. That was the clumsy part, I was thinking of trying to use the filename formula in lieu of using a text reference to the filepath\name, but your method avoids this step altogether. Nice!

I'm not quite sure I understand exactly how this piece of code operates...

#"&A2&"

But it sure appears to create a sheet name that is recognized as such by the Hyperlink function.

Thanks again!

You're welcome. Thanks for the feedback!