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

JLC

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.

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

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

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

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

Kylie D'Costa

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

Jason Anderson

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. 



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

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

 

Josh Garcia

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 imagination) cover my use case perfectly.

Josh

mday

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

Tabitha Hunter

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!!!