Dynamic List of "Live" Jobs

[drewberts]

I have a spreadsheet of all my current projects which have the following columns - job no, job name, fee, start date, status

On a separate sheet (in a separate workbook) I want a dynamic list of jobs which are "live" - which is based on status column.

I have several statuses for jobs and want a list to be dynamic when the statuses are either "LIVE", "PROGRAMMED" or "DELIVERED" The list will also drag through each of the other column values for each job.

I could do this with filters but I need it in a spearate spreadsheet altogether and just want to simply show the list dynamically without any user input. The list needs to work in realtime so that when something changes in the main spreadsheet (in any of the columns) then the list reflects this straight away - e.g. the fee is changed. Also when the status changes either to / from one of the statuses above the row appears / disappears from the list.

I've also thought of a pivot table but would rather it didn't go down this route as I weant no user input at all and to auto update.

Is this possible?

 

[Toadstool]

It simplifies the formula if I can have a worker column on your data sheet to indicate if it's to be selected for reporting:

Book1
	A	B	C	D	E	F
1	job no	job name	fee	start date	status	Report
2	8	Brush cat	$80	6/11/2019	paused	0
3	3	Cut hair	$30	2/16/2019	DELIVERED	1
4	4	Feed turtles	$40	3/11/2019	paused	0
5	2	Lift carpet	$20	1/24/2019	PROGRAMMED	1
6	1	Paint dog	$10	1/1/2019	LIVE	1
7	6	Polish table	$60	4/26/2019	LIVE	1
8	5	Take out trash	$50	4/3/2019	cancelled	0
9	7	Wash shirts	$70	5/19/2019	PROGRAMMED	1
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=IF(OR(E2="DELIVERED",E2="PROGRAMMED",E2="LIVE"),1,0)

Then the reporting Sheet2 would be:

Book1
	A	B	C	D	E
1	job no	job name	fee	start date	status
2	3	Cut hair	$30	2/16/2019	DELIVERED
3	2	Lift carpet	$20	1/24/2019	PROGRAMMED
4	1	Paint dog	$10	1/1/2019	LIVE
5	6	Polish table	$60	4/26/2019	LIVE
6	7	Wash shirts	$70	5/19/2019	PROGRAMMED
7
8
Sheet2

Cell Formulas
Range	Formula
A2:E8	A2	=IF(COUNTIF(Sheet1!$F:$F,1)<ROWS(Sheet2!$A$1:$A1),"",INDEX(Sheet1!A$2:A$999,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$999)-1/(Sheet1!$F$2:$F$999=1),ROW($A1))))

 

[drewberts]

Many thanks @Toadstool - your solution worked a treat