Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Creating formula to insert text data from one cell to use as hyperlink address for other cell

JLC
JLC ✭✭✭✭✭✭
edited 12/09/19 in Archived 2015 Posts

Hi guys, very new to formulas so not sure if this is even possible and/or makes sense.

I have a web form that lets the user submit data for:

 

NEWS PIECE TITLE

NEWS PIECE FILE (UNC path)

 

Then, I send an Update Request to our website team who upload the "NEWS PIECE FILE" from the UNC path onto our website, and provides the HYPERLINK TO NEWS PIECE from our website in another cell.

 

So, I want to combine the cells "NEWS PIECE TITLE" and "HYPERLINK TO NEWS PIECE" into a third cell, which would be the News Piece Title as the text and the Hyperlink to News Piece as the cell's hyperlink.

Is this possible? Please let me know if I need to clarify anything.

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Jaye,

     

    If I understand your description correctly, try this:

     

    =[NEWS PIECE TITLE]23 + ": " + [HYPERLINK TO NEWS PIECE]23

     

    which will take the data in the 23rd row for the two columns and separate them with a colon-and-space for legibility. There can be anything between the quotes, it is just treated as text. 

     

    Hope this helps.

     

    Craig

     

     

  • JLC
    JLC ✭✭✭✭✭✭
    edited 11/26/15

    Hi Craig! thanks for the help. This isn't quite what we're looking for, but it's a good start in the event what I'm looking for isn't actually doable. I think I found a better way to describe what I'm after:

    You know when you right-click on a cell > Hyperlink > Link to URL/Display Text? I'd like a way to automate one cell to be the Link to URL, and another cell to be the Display Text. Any idea if that's possible? Or will I have to open the right-click > Hyperlink dialogue each time?

    Thanks again for your time!

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    edited 11/26/15

    Jaye,

     

    When you have the Hyperlink dialog open, it checks for a valid URL -- so a formula won't work there. 

    Is this Hyperlink going to be in another Webform or end-user interface?

     

    One could probably do this with the API, but that requires a programmer that knows the API. 

     

    I can't think of another way to solve it at the moment. 

    If it comes to me, which it sometimes does, I'll post it.

     

    Craig

     

     

  • JLC
    JLC ✭✭✭✭✭✭

    Thanks Craig, looking forward to any input you may have in the future!

  • Hi Craig,

     

    Quick question on your formula:

     

    =[NEWS PIECE TITLE]23 + ": " + [HYPERLINK TO NEWS PIECE]23

     

    I wanted to join together 3 seperate coloumns. Your forumla works perfectly with two, but when I try and add the third it errors

     

    I have also tried =JOIN formulas but have the same problem.

     

    Am I breaking it by adding the third????


    Thanks Heaps

  • I came across this thread looking for the same solution to the link formation need. 

    Column A is the display text I want to display

    Column B is the URL I want to send them to

    Column C WOULD BE showing ColA's text as a link pointing to ColB's location. 

    Looks like it still can't be done via formulas. sad



    It would be great to have a formula function for this. Like,

    URI([display text],[url for link])

     

  • Giving this conversation a bump as I just ran into another use case.

    I have a sheet aggregating risks and issues via cross-sheet functions. I have reports that correspond to those risks and issues. I hyperlinked those cross-sheet formulas to their corresponding reports. I just realized the formulas have been replaced with flat text.

    In my use case, the URL is static, it's the text that's dynamic. A "hyperlink(text,URL)" function would (in my imaginationwink) cover my use case perfectly.

    Josh

  • I am trying to concatenate a URL and a Smartsheet cell and either have it as a button or Short name (ideally Smartsheet Cell content). Concatenate works but I can't make it pretty with a button or short name?

    The Smartsheet cell contains an index that when added to the Search URL provides us the ability to go straight to tracking page.

    Is there the equivalent of Excel's HYPERLINK function in SMARTSHEETs please?

    Thanks

  • Adding my vote for this functionality!  I need to use cells to create a hyperlink with an alias instead of the long address.  Please add this Smartsheet!!!

This discussion has been closed.