validationgoogle-apps-scriptgoogle-sheetsauto-update

One-to-one assignment in Google sheets with data validation


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.


Solution

  • 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.