My brain is broken (or my formula is)…
We have 4 departments (Production, Creative, Marketing, BD). When a prospective client is in our radar we run their product through a review process to get a sense from a variety of perspectives, the quality of their work.
We submit the materials into an interface and the head of each department assigns a member from their team to the review. Once all 4 heads of assigned and marked their department ‘ready’ an automation kicks in and sends out an email… Sometimes there is 1 person assigned from each department… sometimes there are 2 or more…
So my sheet has (for each department)
- 1 field for the user assignment
- 1 field that looks up the emails (and formats it in the manner expected “graham@email.com, joe@email.com”
finally though, I have a formula feild that attempts to combine all those emails into one string of comma separated emails to that it can be inserted into the to: field of my outbound, automated email.
Problem is… that doesnt work…
When there is more than one person assigned from a department, the comma between departments doesnt exist…
What should look like : “graham@email.com, joe@email.com, david@email.com, charles@email.com, arnold@email.com, steve@email.com”
… instead looks like “graham@email.com, joe@email.comdavid@email.com, charles@email.comarnold@email.com, steve@email.com”
I can’t figure it out. Maybe my use of concatenate is misguided?
Here is the formula
CONCATENATE(IF({Marketing Reviewer(s) Email(s)}=0," “,{Marketing Reviewer(s) Email(s)} & “,”),IF({Biz Dev Reviewer(s) Email(s)}=0,” “,{Biz Dev Reviewer(s) Email(s)}& “,”),IF({Creative Reviewer(s) Email(s)}=0,” “,{Creative Reviewer(s) Email(s)}& “,”),IF({Production Reviewer(s) Email(s)}=0,” ",{Production Reviewer(s) Email(s)}& “,”))
Thanks in advance for any help
