I've searched and can't find a solution in the community.
New staff members complete a form which includes providing an email address. The email is saved in a Contact List column. So far, so good.
Once they are hired and in the HR system, the receive a Net ID, which when combined with the university domain, is their official university email address.
They would have provided a non-university email address in the form, but leadership wants all communication once hired to go to the university email address.
Problem is that I have a Text/Number column with a cell reference to a sheet (that contains a dump of HR information from another system) to pull the Net ID and add the university domain. So it might be asch34@university.edu, where as the email in the Contact List column is artschneider@gmail.com.
Using a formula in the Contact List column, it either displays the email the new staff member provided in the form or the university one, if one was found in the other sheet. However, the university email address appears as text, not as a Contact. And I am finding automated alerts don't go to non-Contact email address.
What's a work around for this, without manually updating the Contact List column with the university email address once they have been hired and have a university email address, so that automated alerts can be sent to the university email address rather than the one they provided initially in the form?
