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)

[TABLE="class: grid, width: 1365, align: center"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Ref [/TD]
[TD]Date Received
[/TD]
[TD]Category[/TD]
[TD]Request [/TD]
[TD]Officer Allocated[/TD]
[TD]Priority
(High, Medium, Low)[/TD]
[TD]Task Scale[/TD]
[TD]Due Date[/TD]
[TD]Date Completed[/TD]
[/TR]
[TR]
[TD]Unique[/TD]
[TD]dd/mm/yyyy[/TD]
[TD]Four option drop down to describe cat type[/TD]
[TD]Blurb about the job[/TD]
[TD]Which person(s)[/TD]
[TD]High, Medium, Low[/TD]
[TD]minor, medium, major, team priority[/TD]
[TD]Expected date to delivery[/TD]
[TD]completed
[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]

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...

[TABLE="width: 326"]
<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>[TR]
[TD="class: xl67, width: 129, bgcolor: transparent"]Open Tasks[/TD]
[TD="class: xl67, width: 84, bgcolor: transparent"]Minor[/TD]
[TD="class: xl67, width: 221, bgcolor: transparent, align: right"]36[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]Medium[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]Major[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]Team Priority[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

With the formulae being
[TABLE="width: 652"]
<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>[TR]
[TD="class: xl67, width: 258, bgcolor: transparent"]Open Tasks[/TD]
[TD="class: xl67, width: 168, bgcolor: transparent"]='Data Lists'!E2[/TD]
[TD="class: xl67, width: 442, bgcolor: transparent"]=COUNTIFS('Current Tasks'!I:I,Dashboard!C19,'Current Tasks'!N:N,"")[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]='Data Lists'!E3[/TD]
[TD="class: xl67, bgcolor: transparent"]=COUNTIFS('Current Tasks'!I:I,Dashboard!C20,'Current Tasks'!N:N,"")[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]='Data Lists'!E4[/TD]
[TD="class: xl67, bgcolor: transparent"]=COUNTIFS('Current Tasks'!I:I,Dashboard!C21,'Current Tasks'!N:N,"")[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]='Data Lists'!E5[/TD]
[TD="class: xl67, bgcolor: transparent"]=COUNTIFS('Current Tasks'!I:I,Dashboard!C22,'Current Tasks'!N:N,"")[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 236"]
<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>[TR]
[TD="class: xl67, width: 86, bgcolor: transparent"]Closed Tasks[/TD]
[TD="class: xl67, width: 129, bgcolor: transparent"]Minor[/TD]
[TD="class: xl67, width: 99, bgcolor: transparent, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]Medium[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]Major[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]Team Priority[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 736"]
<tbody>[TR]
[TD]Closed Tasks[/TD]
[TD]=C19[/TD]
[TD]=COUNTIFS('Current Tasks'!I:I,Dashboard!K19,'Current Tasks'!N:N,">"&(TODAY()-30))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]=C20[/TD]
[TD]=COUNTIFS('Current Tasks'!I:I,Dashboard!K20,'Current Tasks'!N:N,">"&(TODAY()-30))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]=C21[/TD]
[TD]=COUNTIFS('Current Tasks'!I:I,Dashboard!K21,'Current Tasks'!N:N,">"&(TODAY()-30))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]=C22[/TD]
[TD]=COUNTIFS('Current Tasks'!I:I,Dashboard!K22,'Current Tasks'!N:N,">"&(TODAY()-30))[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

Is it possible to do this?

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

Thanks

J