Formula or Automation Help

Im looking for the best way to set up this formula/automation (whichever is best):

I have multiple columns collecting different contact names by title from a form:

I need a formula or automation, that when "Admin Reviewer" = yes, then it takes the contact in "Project Admin" and adds it to another column titled "PT Proof Reviewers", and when "APM Reviewer" = yes, then it takes the contact in "APM" and ALSO adds it to "PT Proof Reviewers", etc.

There are about 8 of these I need to build, that when the reviewer column says yes, it adds the corresponding contact from title column to the "PT Proof Reviewers"

I need the PT Proof Reviewers to be contacts, so that when I set up a proof and go to invite people to the proof, I can just select "PT Proof Reviewers" and all the contacts in that cell will be invited to the proof.

This is the formula im currently working with: If [Admin Reviewer] equals yes then add [Project Admin]contact ", " + if [APM Reviewer] equals yes then add [APM] contact", " + if [PM Reviewer] equals yes then add [Project Manager] contact ", " + if [Director Reviewer] equals yes then add [Director] contact ", " + if [Sr. Director Reviewer] equals yes then add [Sr. Director] contact ", "

I still havent finished this full formula, but once I add more than one, it no longer reports as a contact, just as individual names separated by a comma, and then when I go to my Proof and select invite "PT Proof Reviewers" that column no longer shows as an option (im assuming because there are not contacts in the list, just "text")

Is this possible, does that make sense?

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!