Populate one sheet with data from another sheet based on Shared Status

Hey All,

I’ve been working and learning Monday a lot for the past month, and am struggling to find this solution - if someone knows, please let me know!

I have two boards:

  1. Stakeholder Register
  2. Communications Plan

In the Stakeholder Register, I have captured each stakeholder and their role. I also have a Status column that I use to populate their Audience Cohort (eg. C-Suite, Core Team, Customers, etc.).

Shifting over to the Comms Plan, I list out each item of communication I’ve got to work on (eg. Intro email, meeting request, etc.). I also have a Status column on this board that captures Audience, that’s filled with the exact same list of statuses that are in Audience Cohort status column back on the Stakeholder Register. (Also, yes, I do have “Introductory Email” listed out 5 separate times because of 5 separate audiences - the copy needs to be different, so it needs to be tracked independently.) Right now, I have another Connected Board column that pulls over all the Stakeholders from the Stakeholder column along with their email addresses as a Mirror Column.

Current Workflow: When I enter an item in my Comms Plan and select an Audience I want (eg. C-Suite), I then separately have to select each Stakeholder manually from the Connected Board column linked to make Stakeholder Register in order to get their emails from the mirror column.

Ideal Workflow: When I enter an item in my Comms Plan and select the Audience I want (eg. C-Suites), I’d like to have the all the Stakeholder emails pre-populate based on which Stakeholders have the matching Audience Cohort. So selecting C-Suites should show me all the emails of all the C-Suites in my Stakeholder Register.

Thanks in advance!