Pulling data from a closed workbook based on dates

[damianP]

Hi All - been stuck on this for a day (in between other tasks).

We have a weekly leavers/changes/transfers workbook which we add to daily. I need to be able to pull the data from this workbook into another. I need the first 6 columns (A:F) if the date in column f is sometime last week.

The leavers spreadsheet is: Leavers & Changes 2017- Shared File.xlsx
The second spreadsheet is weekly changes.xslx

The first six columns I would retrieve would be as per below and the date range is specified in the second spreadsheet in cells I1 and I2.

[TABLE="width: 864"]
<tbody>[TR]
[TD]Received Date[/TD]
[TD]Contract[/TD]
[TD]ID[/TD]
[TD]Full Name[/TD]
[TD]Start date[/TD]
[TD]LeaveDate[/TD]
[TD][/TD]
[TD]Date Range[/TD]
[TD="align: right"]30-Jul[/TD]
[/TR]
[TR]
[TD]17/07/2018[/TD]
[TD]Contract 1[/TD]
[TD]12345[/TD]
[TD]Colleague 1[/TD]
[TD]06/03/2017[/TD]
[TD]03/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05-Aug[/TD]
[/TR]
[TR]
[TD]24/07/2018[/TD]
[TD]Contract 2[/TD]
[TD]12352[/TD]
[TD]Colleague 2[/TD]
[TD]20/09/2013[/TD]
[TD]04/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]25/07/2018[/TD]
[TD]Contract 3[/TD]
[TD]12359[/TD]
[TD]Colleague 3[/TD]
[TD]11/06/2018[/TD]
[TD]29/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/07/2018[/TD]
[TD]Contract 4[/TD]
[TD]12366[/TD]
[TD]Colleague 4[/TD]
[TD]07/03/2017[/TD]
[TD]31/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27/07/2018[/TD]
[TD]Contract 5[/TD]
[TD]12373[/TD]
[TD]Colleague 5[/TD]
[TD]29/06/2018[/TD]
[TD]27/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/07/2018[/TD]
[TD]Contract 6[/TD]
[TD]12380[/TD]
[TD]Colleague 6[/TD]
[TD]11/07/2018[/TD]
[TD]30/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/07/2018[/TD]
[TD]Contract 7[/TD]
[TD]12387[/TD]
[TD]Colleague 7[/TD]
[TD]25/09/2017[/TD]
[TD]30/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I've tried this array but it just returns the first value from the first row that meets the date requirement. I know something is wrong - just not sure how to fix.

Thanks in advance

Damian

=IFERROR(INDEX('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$A1:$A9999,SMALL(IF(('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999>=$I$1)*('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999<=$I$2),ROW('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$A1:$A9999),9E+99),ROW(1:1))-1),"")

 

[damianP]

	A	B	C	D	E	F	G	H	I
Colleague 1
Colleague 2
Colleague 3
Colleague 4
Colleague 5
Colleague 6
Colleague 7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7B7B7B]#7B7B7B[/URL] , align: center"]Received Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7B7B7B]#7B7B7B[/URL] , align: center"]Contract[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7B7B7B]#7B7B7B[/URL] , align: center"]ID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7B7B7B]#7B7B7B[/URL] "]Full Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7B7B7B]#7B7B7B[/URL] , align: center"]Start date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7B7B7B]#7B7B7B[/URL] , align: center"]LeaveDate[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=7B7B7B]#7B7B7B[/URL] , align: center"]Date Range[/TD]
[TD="align: right"]30-Jul[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]17/07/2018[/TD]
[TD="align: center"]Contract 1[/TD]
[TD="align: center"]12345[/TD]

[TD="align: center"]06/03/2017[/TD]
[TD="align: center"]03/08/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]05-Aug[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]24/07/2018[/TD]
[TD="align: center"]Contract 2[/TD]
[TD="align: center"]12352[/TD]

[TD="align: center"]20/09/2013[/TD]
[TD="align: center"]04/08/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]25/07/2018[/TD]
[TD="align: center"]Contract 3[/TD]
[TD="align: center"]12359[/TD]

[TD="align: center"]11/06/2018[/TD]
[TD="align: center"]29/07/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]26/07/2018[/TD]
[TD="align: center"]Contract 4[/TD]
[TD="align: center"]12366[/TD]

[TD="align: center"]07/03/2017[/TD]
[TD="align: center"]31/07/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]27/07/2018[/TD]
[TD="align: center"]Contract 5[/TD]
[TD="align: center"]12373[/TD]

[TD="align: center"]29/06/2018[/TD]
[TD="align: center"]27/07/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]30/07/2018[/TD]
[TD="align: center"]Contract 6[/TD]
[TD="align: center"]12380[/TD]

[TD="align: center"]11/07/2018[/TD]
[TD="align: center"]30/07/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]30/07/2018[/TD]
[TD="align: center"]Contract 7[/TD]
[TD="align: center"]12387[/TD]

[TD="align: center"]25/09/2017[/TD]
[TD="align: center"]30/07/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

leavers​

Hi I've added a screen shot to help visualise the issue.

Thanks

Damian

 

[damianP]

I've managed to fix some of this by amending the formula in each row (as below) and sorting the original workbook by date. However it's pulling more data now than I need. It's giving me data through to September when I only need any leavers between 30th July and 5th August. Any thoughts please?

Column A:
=IFERROR(INDEX('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$A1:$A9999,SMALL(IF(('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999>=$I$1)*('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999<=$I$2),ROW('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$A1:$A9999),9E+99),ROW(1:1))-1),"")
Column F:
=IFERROR(INDEX('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999,SMALL(IF(('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999>=$I$1)*('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999<=$I$2),ROW('[Leavers & Changes 2017- Shared File.xlsx]Leavers'!$F1:$F9999),9E+99),ROW(1:1))-1),"")