Preventing a cell from being populated if another cell has been populated


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 put in a data validation into the cells so that the only option that can be selected is "unit selected", 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?

I hope that makes sense? 

Thank you in advance for your help!





There are a few ways to do this, but it is not an easy task unless you are a proficient user of Excel.

Basically, this is what you have to do:

  1. For each Unit you need to create a list of options that work for that Unit.
  2. Then, you need to name each list with the exact name of the Unit that will appear in the first drop-down menu.
  3. Create your first drop-down menu as usual.
  4. When making the second or "dependent" drop-down menu, you need to put =INDIRECT(A1) where A1 is the location of the first drop-down menu.


Sadly, I am not all that proficient at Excel.  I did manage to put in a formula to the effect of =IF(A1<>"", "incompatible", " ") which worked fine for the first lot of units, but when it came to putting in the same formula for the second lot which are obviously equally incompatible with the first lot, it says that the formula is a circular reference.  So perhaps I'm just going to have to pick one lot and hope the assessors can figure it out for themselves lol.  Thank you very much for your answer but I didn't really understand what you were suggesting I do.  I'm sure that's my fault being that I'm not really as proficient as I'd like to be!  Thanks again!
Tess Mar 2, '17 at 7:29 am
I assumed you were using drop-down menus/lists. If you edit your question and upload a sample spreadsheet, it would be easier to help you or at least point you in the right direction.
don (rep: 1665) Mar 2, '17 at 8:06 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login