Hide a value in a cell unless another cell has a given value

Question

I'm trying to create a formula to generate a list of values in a dropdown.

1. Each row contains a task that has a status, and a priority.
2. The priority is set from a dropdown containing a list of numbers from 1-50.
3. No two tasks can share the same priority so the dropdown must exclude any already in use.
4. When a task has a status="done", its priority is no longer considered in use and should once more be available in the dropdowns.

For example, in the table below,

• the dropdowns are in column A
• The priority 15 in A4 should not be excluded from dropdowns because C4="Done"
• Therefore, the dropdown in A6, for example, could include 15 but would not include any of {24, 32, 49}

	A	B	C
1	Priority	Task	Status
2	32	A task to complete	In Progress
3	24	Another task	In progress
4	15	A completed task	Done
5	49	A postponed task	On Hold
6		Another task awaiting prioritisation	To Do

I'm able to eliminate any assigned priority from the dropdowns using the following formula, but I can't get to the next step, restoring used values to the dropdown if they are assigned to a task with status="done":

=ARRAYFORMULA(
   IF(
     COUNTIF(
       H2:H1000,A2:A1000)=0,
       H2:H1000,))

How would I reinstate a value that was used, such as in A4 of my example, if the status of that row is set to "Done"?

Answer 1

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.