Converting Cell Text to Hyperlink

Art Schneiderheinze

Hi. I have a formula in a cell that generates the name of a course using text from other cells (e.g., BUS-430-BL-6W-1D-Master).

FORMULA:

=LEFT([Subject + Course]2, FIND(" ", [Subject + Course]2) - 1) + "-" + RIGHT([Subject + Course]2, LEN([Subject + Course]2) - FIND(" ", [Subject + Course]2)) + "-" + IF(NOT(ISBLANK(Partnership2)), Partnership2 + "-") + UPPER([Modality(s)]2) + IF(FIND("BL", [Modality(s)]2) > 0, "-1D", "") + IF(FIND("OG", [Modality(s)]2) > 0, "-2D", "") + "-Master"

However, this just generates the name of the course.

Ideally, we would like then to make this a hyperlink that takes a user to the course in our learning management system (e.g., https://d2l.nxl.edu/d2l/home/43940). The URL is the same for all courses, except for the last 5 digits, which is generated by the learning management system. 

Any idea how we can make the course name (generated from the formula) into a hyperlink (using the fixed portion of the URL plus the unique 5 digit number)? 

In the past, we manually entered the course name and made it a hyperlink (manually entering the URL by copying/pasting from the browser). Now, we having Smartsheet generate the name of the course, but not sure the best way to make it a hyperlink.

This is what we woudl like the cell to display: BUS-430-BL-6W-1D-Master

Thanks!

Paul Newcome

Unfortunately formulas can't be made into a hyperlink. Your options would be to include a general hyperlink somewhere on the sheet along with instructions on how to find the specific course, or manually enter it into another cell.

Andrée Starå

Hi,

I would research if it's possible to do it thru the API.

https://developers.smartsheet.com/

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Erik Beltran

This is indeed possible using the API.

I set up an automated task using C# - if you are interested in the solution, dont hesitate to reach out.

 

Thanks,

Erik

Cory Strischek

@Erik Beltran - If you can provide contact info or post the outline of your API solution here, much appreciated.

Illes M

@Erik Beltran +1 here

Matt Seitz

AJW

Hi, Eric. I know this is an old string, but is there any chance that you could share your automated task with me? I've not seen anywhere that Smartsheet has added this functionality yet.

Thanks!!

https://community.smartsheet.com/discussion/comment/228566#Comment_228566

Adrian Mandile CHESS

Hi @Art Schneiderheinze, @AJW, @Genevieve P., @Paul Newcome,

I know this is an old thread, but I have been able to calculate a formula to do what you are trying to do as a clickable URL – just not with a neat Display Text however,

= "https://domain.sharepoint.com/folder/" + [Folder Name]@row

If your URL root is the same for all items in the sheet, then create a Sheet Summary field called "SharePointURL" (or "FolderURL" or something like that) and change the column formula to...

= SharePointURL# + [Folder Name]@row

It will display the full https text, but it is clickable.