Vlook-up with date conditions

[johnny_doyle]

Hi Team,

I have a workbook that I am trying to create and an overall Dashboard to give a snapshot of information from my main list of projects. On the main sheet we enter details about the project, unique reference numbers, and dates to show start & end of projects. There are also sections for team Scale (minor, medium, major, team priority)

C	D	E	F	G	H	I	M	N
Ref	Date Received	Category	Request	Officer Allocated	Priority (High, Medium, Low)	Task Scale	Due Date	Date Completed
Unique	dd/mm/yyyy	Four option drop down to describe cat type	Blurb about the job	Which person(s)	High, Medium, Low	minor, medium, major, team priority	Expected date to delivery	completed

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>

The rows jump from I to M as there is some conditional formatting supporting cells hidden that are used to generate a due date, i.e. Date Received +Priority = Due Date.

This section is working fine, however with the idea of the dashboard, I would like to show current open tasks and recently completed tasks on the dashboard (not filtering this page) So my dash has this...

Open Tasks	Minor	36
	Medium	6
	Major	2
	Team Priority	1

<colgroup><col style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;" width="129"> <col style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;" width="84"> <col style="width: 166pt; mso-width-source: userset; mso-width-alt: 8082;" width="221"> <tbody>
</tbody>

With the formulae being

Open Tasks	='Data Lists'!E2	=COUNTIFS('Current Tasks'!I:I,Dashboard!C19,'Current Tasks'!N:N,"")
	='Data Lists'!E3	=COUNTIFS('Current Tasks'!I:I,Dashboard!C20,'Current Tasks'!N:N,"")
	='Data Lists'!E4	=COUNTIFS('Current Tasks'!I:I,Dashboard!C21,'Current Tasks'!N:N,"")
	='Data Lists'!E5	=COUNTIFS('Current Tasks'!I:I,Dashboard!C22,'Current Tasks'!N:N,"")

<colgroup><col style="width: 194pt; mso-width-source: userset; mso-width-alt: 4717;" width="258"> <col style="width: 126pt; mso-width-source: userset; mso-width-alt: 3072;" width="168"> <col style="width: 332pt; mso-width-source: userset; mso-width-alt: 8082;" width="442"> <tbody>
</tbody>

And that counts the number of current tasks on the books. My issue is now two fold. I want a separate Open tasks and close tasks sheet which brings back the content of only those tasks that are Major or Team Priority. So I came back to this formula

{=IF(ROWS(C$21:C21)>(D$21),"",INDEX('Current Tasks'!C:C,SMALL(IF('Current Tasks'!I:I=$C$21,ROW('Current Tasks'!I:I)),ROWS(C$21:C21))))}

Using the unique number as a Vlookup to populate the other cells. My issue is that the sheet stops once it hits the number of tasks counted and I cannot get it to take the Completed Date Value in to consideration for open and closed tasks.

Is it possible to modify so that only current tasks (with no completed date being the reference) are returned, skipping any that are closed? Currently the sheet will not 'jump over' a closed task that meets the major header. So if tasks 1 was major and open, tasks 2 major and complete, and task 3 major and open, using the above fomulae, only tasks one and two are returned.

The next issue would be those that are closed. We don't want to have all closed tasks, just those that were closed within the last 30 days.

Closed Tasks	Minor	5
	Medium	2
	Major	1
	Team Priority	0

<colgroup><col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;" width="86"> <col style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;" width="129"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;" width="99"> <tbody>
</tbody>

Closed Tasks	=C19	=COUNTIFS('Current Tasks'!I:I,Dashboard!K19,'Current Tasks'!N:N,">"&(TODAY()-30))
	=C20	=COUNTIFS('Current Tasks'!I:I,Dashboard!K20,'Current Tasks'!N:N,">"&(TODAY()-30))
	=C21	=COUNTIFS('Current Tasks'!I:I,Dashboard!K21,'Current Tasks'!N:N,">"&(TODAY()-30))
	=C22	=COUNTIFS('Current Tasks'!I:I,Dashboard!K22,'Current Tasks'!N:N,">"&(TODAY()-30))

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Is it possible to do this?

I know I am asking a lot!! Any advice would be gratefully received.

Thanks

J