1

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"?

0

1 Answer 1

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.
1
  • 1
    perfect, thanks for that, I hadn't considered the sequence function! Feb 26 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.