Automatic Convert Email Address to Hyperlink

Art Schneiderheinze

Anyway Smartsheet can recognize the value in a cell as an email adreas and convert it to a hyperlink?

Based on value in a cell in Sheet 2, an email address is "pulled" from a cell in Sheet 1. However,  its text, not an email address contact/hyperlink. 

Ultimately,  I need to have a way Smartsheet can use the email address to send an alert based on the value of another cell. 

I enter course code in a cell in Sheet 2. A formula in another cell in Sheet 2 pulls email address from cell in Sheet 1. Another cell in Sheet 2 is updated as "on track" or "not on track. If set to later, an alert is automatically sent to the email address that they need to attend a weekly meeting to resolve an issue and get course back on track.

Thanks for any suggestions!!!

Andrée Starå

Hi,

Unfortunately, it's not possible at the moment because it's not possible to have formulas in contact columns, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

There might still be a way to do it. How many e-mail addresses is it? Are they changing or always the same?

Depending on the answer, it could make sense to set something up with specific alerts or a third-party tool like Zapier or similar.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Art Schneiderheinze

I created a Course Development tracker sheet that pulls data from a variety of sheets, including the name of program leader and SME. On their respective sheets, I included their e-mail address. So, each term, when we add new courses for development/re-development to the tracker sheet, it pulls the program leader/SME names from the other sheets.

If I pulled the e-mail addresses, they wouldn't be hyperlinks (or assigned to contacts) in the tracker sheet. So, I wouldn't be able to take advantage of the alerts tools, would I? Or could there be some kind of work around?

A little bit about my rationale: I have the list of SMEs available (i.e., completed training, etc.) from the SME sheet autopopulate in a drop-down in the tracker sheet. If the SME's name isn't in the drop down that the program leader requests, it let's us know we need to on-board the SME.

The program leader information is also maintained on a separate sheet, which associates the program leader with the course prefixes in his or her program. This way, we know automatically, based on the course code entered into the tracker sheet, who the program leader is.

I have a team of instructional designers and am also working with a few third-party vendors to develop the courses. I want them to update a status cell for each course assigned to them. If the status is "not on track", it sends an alert to the program leader requiring them to attend a meeting at the end of the week to resolve the issue.

Thoughts on a work around? I am not familiar with Zapier but am open to it as a solution if it accomplishes what I want to accomplish (and not cost money).  

Andrée Starå

How many e-mail addresses is it? Are they changing or always the same?

How many program leaders are there?

Best,

Andrée

Art Schneiderheinze

There are not many but I want to avoid limiting the number. Over time, there could be around 50 SMEs and 20 or so program leaders. All of these people are apparently integrated already into our Smartsheet (they show up in a drop down if the column is a Contact column. Problem is I don't want to manually enter them.

Sheet 1: The list of courses to be developed

Sheet 2: List of program leaders (names, e-mail addresses, course prefixes associated with a PL)

Sheet 3: List of SMEs (names and e-mail addresses) who have received on-boarding training.

Program leaders are automatically added in Sheet 1 (from the list of names/emails) in Sheet 2 after a course code is entered in Sheet 1. (The formula I wrote looks in Sheet 2 for the course prefix and identifies which program leader "owns" courses with that prefix.)

SMEs are selected in Sheet 1 via a drop-down box, which is dynamically loaded from a list of SMEs in Sheet 3. This helps us make sure that a SME assigned has received appropriate training or if they are new to the role.

In Sheet 1, we will update status of the course. If by noon on Thursday of each week the status is "Not on Track", a column in Sheet 1, we want Smartsheet to alert the program leader via e-mail (the PL identified in Sheet 1, using e-mail listed for the PL in Sheet 2). 

I assume to use the alert, the PL and SME must be contacts in Sheet 1?

Andrée Starå

Hi,

In Sheet 1, we will update status of the course. If by noon on Thursday of each week the status is "Not on Track", a column in Sheet 1, we want Smartsheet to alert the program leader via e-mail (the PL identified in Sheet 1, using e-mail listed for the PL in Sheet 2). 

I assume to use the alert, the PL and SME must be contacts in Sheet 1?

They don't need to be contacts/shared in the sheet, but the Alert & Actions Permissions has to allow to send to them. My suggested workaround would be to add a contact column with the PL and have an alert send to that contact.

Would that work?

Best,

Andrée

Art Schneiderheinze

Yes but the PL contact information is referenced in another sheet. So, in Sheet 1 the cell uses MATCH/INDEX to find the correct program leader's name in Sheet 2 to put in Sheet 1.

I could also MATCH/INDEX the email address (from Sheet 2) but its not then a Contact columm. So would the alert just be able to refer to an email address in a Text column, or would I somehow need to get that in a Contact column.

Andrée Starå

Unfortunately, you or someone else would have to update the contact column manually. You could automate it with an update request to make it easier.

Another way could be to set up an alert for each PL (send to their email) and then trigger it with the email address in the sheet (but only to match to the alert, not to send)

Would that work?

Best,

Andrée

Andrée Starå

Hi,

Formulas in contact columns. It's released now! 

https://community.smartsheet.com/announcement/announcing-formula-support-contact-columns

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting