Formula to fill in the information in another column

KoryF

I have the RPs, OLH Assignments and System Roles added as a dropdown list. I am trying to find a formula that will add the OLH Assignment and System Role based on what is checked in the Associated RPs column.

Ex: If "Clinical Manager" is added to the Associated RPs column, then "Clinical Supervisor Home Health Curriculum; F2F Coordinator Curriculum; Quality Assurance Curriculum" will be added to "OLH Assignment Column" and "Clinical Manager" is added to "System Role" Column.

Additionally, if "Clinical Manager" and "Patient SVCS Coordinator" are added in Associated RPs column, then "Clinical Supervisor Home Health Curriculum; F2F Coordinator Curriculum; Quality Assurance Curriculum" and "Scheduler Home Health Curriculum; Therapy Coordinator Curriculum" would be added to the OLH Assignment Column, and "Clinical Manager" and "Patient Services Coordinator" added to the System Role Column

There are several more options in the dropdown, this is just an example

Paul Newcome

I would suggest building out a table where you have the RPs in one column (multiple entries for each), the Assignments in a second column, and the System Roles in a third column. Then you could use a JOIN/COLLECT to pull each together.

=JOIN(COLLECT({Reference Table Assignment Column}, {Reference Table RP Column}, HAS([Associated RPs]@row, @cell), CHAR(10))