Return to 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's 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}

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

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}

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

I'm trying to create a formula to generate a list of values on a dropdown. The purpose is to allow a customer to select a unique priority for a task on a given row unless the status for that task is Done.

I'm able to make the selection unique using =ArrayFormula(IF(countif(H2:H1000,A2:A1000)=0,H2:H1000,)) but I can't get to the next step - how would I reinstate a value that was used if the status of that row were set to "Done" ? I've made the output come up as TRUE or FALSE but that's not what I'm after, I'm using numbers from 1 to 50.

For example: A customer selects priorities for a list of tasks and uses the values 1, 2, 3, 4. For the next row, those values would not be selectable as they would be hidden from the range by the formula above. What I want to do is add back any value that was selected but that also has a status of "Done" on the same row, so if I change the status of a task with priority 3 to Done, the number 3 would become available again in the dropdown.

In the dropdown menu for the Priority column, the values 1, 2, 3, and 4 are currently hidden on the 5th row. However, I want the task in row 3 to allow the number to be used again as the task has been completed.

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's 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}

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

I'm trying to create a formula to generate a list of values on a dropdown. The purpose is to allow a customer to select a unique priority for a task on a given row unless the status for that task is Done.

I'm able to make the selection unique using =ArrayFormula(IF(countif(H2:H1000,A2:A1000)=0,H2:H1000,)) but I can't get to the next step - how would I reinstate a value that was used if the status of that row were set to "Done" ? I've made the output come up as TRUE or FALSE but that's not what I'm after, I'm using numbers from 1 to 50.

For example: A customer selects priorities for a list of tasks and uses the values 1, 2, 3, 4. For the next row, those values would not be selectable as they would be hidden from the range by the formula above. What I want to do is add back any value that was selected but that also has a status of "Done" on the same row, so if I change the status of a task with priority 3 to Done, the number 3 would become available again in the dropdown.

I'm trying to create a formula to generate a list of values on a dropdown. The purpose is to allow a customer to select a unique priority for a task on a given row unless the status for that task is Done.

I'm able to make the selection unique using =ArrayFormula(IF(countif(H2:H1000,A2:A1000)=0,H2:H1000,)) but I can't get to the next step - how would I reinstate a value that was used if the status of that row were set to "Done" ? I've made the output come up as TRUE or FALSE but that's not what I'm after, I'm using numbers from 1 to 50.

For example: A customer selects priorities for a list of tasks and uses the values 1, 2, 3, 4. For the next row, those values would not be selectable as they would be hidden from the range by the formula above. What I want to do is add back any value that was selected but that also has a status of "Done" on the same row, so if I change the status of a task with priority 3 to Done, the number 3 would become available again in the dropdown.

In the dropdown menu for the Priority column, the values 1, 2, 3, and 4 are currently hidden on the 5th row. However, I want the task in row 3 to allow the number to be used again as the task has been completed.