Conditional Data Validation List based on table

[SBiebaut]

Hello all,

I am trying to create a drop down list off all projects from a table, but based on their status. If a project is live, it should not appear in the dropdown list
On sheet 1 I have a table with projects and their status

Project	Status
Horse	In Progress
Cow	In Progress
Pig	Live
Chicken	On Hold
Duck	Live

on sheet 2, we organise the projects that are in the backlog. Now, projects that have already gone live should not appear here, and should not be an option in the dropdown. in the example above, the drop down list should only show Horse, Cow and Duck.
I've been trying to achieve this in the "Data Validation" --> "list" and then work with IF. However, I've only managed incorrect formulas. Is this at all possible? and if so, how exactly?

 

[Fluff]

Hi & welcome to MrExcel.
You could add a helper cell on sheet1 like

+Fluff 1.xlsm
	A	B	C	D
1	Project	Status
2	Horse	In Progress		Horse
3	Cow	In Progress		Cow
4	Pig	Live		Chicken
5	Chicken	On Hold
6	Duck	Live
7
8
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(FILTER(Table2[Project],Table2[Status]<>"Live"))
Dynamic array formulas.

And the use

=Sheet1!D2#

in your DV

 

[etaf]

Ignore - answered at same time
although
why not Pig ?

the drop down list should only show Horse, Cow and Duck.

so in that "In Progress" & "Live"