Skip to main content
Added explanation
Source Link
Blindspots
  • 7.5k
  • 5
  • 20
  • 38

Put this formula somewhere on the same sheet or another sheet and point your dropdown to the column where you place it.

=FILTER(
   SEQUENCE(50),
   COUNTIFS(
     A2:A1000, SEQUENCE(50),
     C2:C1000, "<>Done")=0)

Explanation

  1. COUNTIFS is used in place of COUNTIF to allow adding the additional criteria <>Done.
  2. SEQUENCE is used to create the list of numbers from 1-50
  3. FILTER removes numbers already in use based on the array of TRUE/FALSE values returned by COUNTIFS.

Put this formula somewhere on the same sheet or another sheet and point your dropdown to the column where you place it.

=FILTER(
   SEQUENCE(50),
   COUNTIFS(
     A2:A1000, SEQUENCE(50),
     C2:C1000, "<>Done")=0)

Put this formula somewhere on the same sheet or another sheet and point your dropdown to the column where you place it.

=FILTER(
   SEQUENCE(50),
   COUNTIFS(
     A2:A1000, SEQUENCE(50),
     C2:C1000, "<>Done")=0)

Explanation

  1. COUNTIFS is used in place of COUNTIF to allow adding the additional criteria <>Done.
  2. SEQUENCE is used to create the list of numbers from 1-50
  3. FILTER removes numbers already in use based on the array of TRUE/FALSE values returned by COUNTIFS.
Source Link
Blindspots
  • 7.5k
  • 5
  • 20
  • 38

Put this formula somewhere on the same sheet or another sheet and point your dropdown to the column where you place it.

=FILTER(
   SEQUENCE(50),
   COUNTIFS(
     A2:A1000, SEQUENCE(50),
     C2:C1000, "<>Done")=0)