Preventing a cell from being populated if different cell is popu

TessF Posts 1 Registration date Wednesday March 1, 2017 Status Member Last seen March 1, 2017 - Mar 1, 2017 at 05:52 AM
Hi there,

I have created a spreadsheet for units for a Diploma that assessors can use to select various units from a number of possible options. I have used a data validation for the cells so that the only option that can be selected is "unit selected" if they wish to choose a particular unit, or they can leave it blank if that particular unit is not being selected.

Because there are some units that are incompatible with others, I wanted to know if there was a way of preventing the incompatible unit being selected if the assessor had already chosen a unit which would be incompatible with the second unit they are wanting to select. i.e. Unit CMM01 is incompatible with unit BVF5, if the assessor has used the dropdown menu for unit CMM01 and chosen "unit selected", is there a way that I can remove the option of unit BVF5 being selected also? Or putting in some kind of IF formula so that if cell O50 is populated with "unit selected", then it automatically come back with "incompatible" or something like that. I'm not an expert on Excel and I've tried using an IF formula in the incompatible cell but it just comes back with #NAME or something similar.

Please help!

I hope that makes sense?

Thank you in advance for your help!