Conditional Formatting or Conditional Lists

Question

What I am trying to do seems like it should be simple bit I can't get it to work.

Ideally, I'd like to be able to create a list of my staff and remove certain ones from the roster if they are selected OR if they are not allowed to work together.

Also acceptable is conditional formatting where it will highlight those who cannot be together.

Since I can't seem to upload, here's a google sheet with explanations and examples.

My staff list is actually fairly large so that's why I would like this.

https://docs.google.com/spreadsheets/d/1aBmb-2eDK9n9CeElOQntbyVuUe382UkIF3n5wuMozj8/edit?usp=sharing

Answer 1

Hello CG_650,

Unfortunately, I’m really sorry I don't have any further troubleshooting to offer.

There are many knowledgeable users active on the forum and I hope that someone else can offer further insight into your issue.

Thank you, Ibhadighi

Answer 2

Ok, so I think I am doing very poorly.

I've updated the google sheet with more pictures. I do know the ranges are different in the list of failed formulae. Again, these were small scale experiments.

If anyone can look at what I'm doing and assist, I'd very much appreciate it.

-C

https://docs.google.com/spreadsheets/d/1aBmb-2eDK9n9CeElOQntbyVuUe382UkIF3n5wuMozj8/edit?usp=sharing

Answer 3

Apologies all. I made is mistake before leaving for the day in the sheet. Thank you for responding even without the information.

I fixed the sheet so viewers can see now.

I'm still learning. Also, I am known to overcomplicate things so hopefully this works. I will try to adapt the above and let you know if it works.

Answer 4

Hi there

This is what happens when we click the link.

Regards

Jeovany

Answer 5

Hello CG_650,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

To achieve your goal of creating a list that automatically removes certain staff members based on selections or highlights staff who cannot work together using Google Sheets, you can use a combination of custom formulas in Conditional Formatting and data validation drop-downs for selections. Here’s a simple guide on how to set this up:

For Highlighting Staff Who Cannot Work Together:

1. **Use Conditional Formatting**:
   • Select your staff list range.
   • Go to Format > Conditional formatting.
   • Use a custom formula to determine which staff members cannot work together. For example, if staff members who cannot work together are listed in a separate table, your formula might look something like =MATCH(A2, INDIRECT("ProhibitedPairs! A:A"), 0), assuming A2 is the first cell in your staff list and ProhibitedPairs is the name of the sheet with prohibited pairs listed.
   • Set the format style to highlight these cells.

For Automatically Excluding Selected Staff Members:

1. **Create a Dynamic List with a Filter Function**:
   • Assume you have a selection or input column where you indicate (e.g., with an "X") which staff members are selected.
   • Use a FILTER function in another column or sheet to list only those staff members who are not selected. For example, =FILTER(StaffList! A2:A, NOT(ISNUMBER(MATCH(StaffList! A2:A, SelectedStaff! A2:A, 0)))), assuming StaffList! A2:A is your full staff list and SelectedStaff! A2:A contains the names of selected staff.

For Excluding Staff Based on Selections and Restrictions: Combine the above methods by adjusting the FILTER function to take into account both selections and any restrictions (like not allowed to work together).

**Note**: Custom formulas will depend on your specific setup, including how you're indicating selections and how you've structured the information about who cannot work together. Without access to your specific Google Sheet, the examples provided are based on general assumptions.

This setup allows for a fair amount of flexibility in managing your staff roster, letting you highlight incompatible pairs or exclude individuals based on selection or rules. Remember, Google Sheets functions like FILTER, MATCH, and INDIRECT can be very powerful for creating dynamic lists based on conditions.

I hope this helps.

Best Regards, Ibhadighi