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

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

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