Hyperlink to corresponding row in external document

22Wordsmith

I have a 600 row Smartsheet and need to provide hyperlinks for each row to an external Excel document. At the moment, a basic hyperlink will open Excel but place the cursor at the top of the sheet. Can I create a hyperlink formula to match the reference numbers on each row?

Link for Reference 010 opens Excel document with cursor at Reference 010.

I tried to insert a Reference@Row but it came back as UNPARSEABLE. This is the formula I tried to use:

=Hyperlink("PATH to MY DATA",{Reference@row})

Grateful for any guidance.

KPH

Good news and bad news.

You can link to the individual row. You will find the URL if you click on the row menu (three dots next to the row number), choose properties, and then copy the row link.

However, there is not a hyperlink function in smartsheet and unless you have an API (or, I believe Bridge) you can't exact these URLs in bulk (so 600 would take a while).

https://community.smartsheet.com/discussion/95003/hyperlink-function

https://community.smartsheet.com/discussion/115371/row-link-column

22Wordsmith

Hi @KPH That's great to read but I need the link to go in the other direction. Is there a way I can build a hyperlink in Smartsheet that opens to a specific cell in Excel?

KPH

There is not a hyperlink function in smartsheet so you can’t add a display name without right clicking on the cell, but If you can create a column of references to excel you could combine that with the start of the url using a simple + to join text strings. So long as the resulting string looks like a url (starts http or www, for example), the result will be clickable. Is that what you are looking for?

22Wordsmith

Sorry, @KPH, without an example, I couldn't understand your response.

KPH

You could add a column to your sheet with the excel cell references called reference and then use something like this in the smartsheet cell you want the link to appear in. (You would use straight quotes, but I’m on my phone so can’t and include the full path to your file where I have dots, and sheet name where I have sheet1)

=“https://……..xls#sheet1!”+reference@row

KPH

Did that make sense @22Wordsmith ? Is it what you are trying to do?

22Wordsmith

Thanks, @KPH . I haven't quite got it to work but am multitasking to great detriment all around at present. I will get the rest of the system launched and come back to this. I appreciate your assistance.