Google Sheets – How to Tag Cells with Multiple Tags from a List

This appears to require a script (Tools > Script editor) because the validation rules available through the interface don't seem to support "in this list but not in that list". Here's a script that assumes the options are in cells A1:F1 and the dropdowns are in cells A2:C2. (It is a bit easier to code this logic with row-oriented data.)

Depending on your use case, you may decide to hardcode the options into the script; this is what I pointed out in a comment with the line var options = ...

The logic of the script is:

1. get options available
2. get options chosen ("rankings" in the script)
3. To compute the validation rule for cell I,
   • remove the value of this cell from rankings (.splice does this)
   • remove the remaining rankings from the list of available options (.filter does this)
4. Set the validation rule, prohibiting invalid values and showing some help text.
5. The above steps are executed when the document is first open, and then after each edit.

function onOpen() {
  onEdit();
}

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var options = sheet.getRange("A1:F1").getValues()[0];  // or var options = ['q','w','e','r','t','y'];
  var ranking = sheet.getRange("A2:C2");  
  var n = ranking.getWidth();
  var current, remaining, rule; 
  for (var i=0; i<n; i++) {
    current = ranking.getValues()[0]; 
    current.splice(i,1);
    remaining = options.filter(function(j) {return current.indexOf(j) < 0});
    rule = SpreadsheetApp.newDataValidation()
                         .requireValueInList(remaining)
                         .setAllowInvalid(false)
                         .setHelpText('Choose distinct options')
                         .build();
    ranking.getCell(1,i+1).setDataValidation(rule);    
  }  
}

Use conditional formatting in cell J3 with the formula =AND(ISBLANK(F3)=FALSE,G3>0)

Step-by-step:

1. Select cell J3 and click on Format > Conditional formatting
2. Select the options as in the screenshot below and paste the above formula in the required field.

Conditional formatting is applied if the formula results to TRUE. This will only be the case if both the cell F3 is not be blank (= contains data) and if cell G3 is higher than 0.