Is it possible to add a data validation dropdown to each cell in a column such that available values are consumed and removed from the dropdown once selected, so that only one cell can receive a given value and there are no duplicate selections? I'm not interested in highlighting duplicates, I want to remove a value from available options once selected by another cell.
I would approach it by adding another sheet to your spreadsheet. Lets call this sheet Settings. In that sheet, list all the available option in Column A. Then in Column B add a formula which checks, if that particular value has already been selected. If it is, then just make it empty, otherwise return its value.
=IF( ISNUMBER(MATCH(A1, *theColumnWhereTheDropDownIst*, 0)), "", A1 )
So now, in Column B, only those values will be shown which are still free. Lets name this range "unusedValues".
Then, in your range where you want the pick-list, add data-validation and set it to "unusedValues". It should now work.
This approach is easy to use and modify. It does not require apps-script.