Using a formula to modify the contents of a multi-Contact cell

Afleisc

Hello,

Here's the idea: Use a formula that when given a contact cell with multiple assigned contacts, and a secondary cell with one assigned contact, it will SUBTRACT (or otherwise modify) that contact from the first cell. So in practice it would look like this:

Cell 1: Contact1, Contact2, Contact3

Cell 2: Contact2

Cell 3: Use a formula that subtracts out Cell2's value from Cell1's value, returning the values of only Contact1 and Contact3

The reason this has come up is that I currently oversee numerous teams comprised of 2-3 people per team. One team is "on point" per week with each team rotating in on a continual basis throughout the year. Tasks get added to the Sheet with automated notifications going out to the "on point" team only so they can self-assign the task. I was wanting a way to have the Sheet send an automated notification to the remaining members of the "on point" team so they know the task has been claimed without having to login to the master Sheet to see that. I also wanted to avoid sending updates to every person shared to the Sheet as that would spam teams that are currently not "on point."

Nik Fuentes

so something like REMOVE( value , text , [text2, ... ] ), with the 'value' being a multi-select cell and the 'text' being the option to remove if present?

If I understand correctly, the original value in Cell1 would remain unchanged, but the list displayed in Cell3 would omit the contents of cell2, yes?

Afleisc

Hi Nik,

You've got the concept correct regarding what would display in each cell, but the cell type I'm referring to is a Contact cell rather than a multi-select cell. And I was actually thinking it would be really nice to be able to just use a mathematic operation to accomplish this with the hitch being how to treat contacts like integers. So placing a basic formula into CELL 3 like: =CELL 1 - CELL 2