Afternoon,
I have been fruitlessly searching for a way to do this, i've partially managed to work it but cannot get any further.
I have a spreadsheet where i log FoI requests. These have a 20 day time limit and we send reminders out after 10, 15, 17, 19 20 (due) & 21 (overdue) days. I am trying to create a list on a seperate tab of all those that are within their reminder period but haven't been closed or sent a reminder.
I have used the following formula:
[TABLE="width: 1160"]
<TBODY>[TR]
[TD]INDEX(RequestLog!B11:AC1008,MATCH(1,(RequestLog!AD11:AD1008="")*(RequestLog!Y11:Y1008="")*(RequestLog!B11:B1008<11)*(RequestLog!B11:B1008>5),0),2)
</SPAN>
This will show me the first instance of a reference needed a reminder at the 10 day point (i have managed to modify this for the other reminder points). I now want to combine this with the SMALL & ISERROR functions, copy it down, and produce a list.
Is anybody able to help me ? I have pasted below an extract from the main table (this shows column B through to AD, what is shown as a P should be in Wingdings and show a tick)
[TABLE="width: 1507"]
<TBODY>[TR]
[TD]Days remaining</SPAN>[/TD]
[TD]Ref</SPAN>[/TD]
[TD]Date
received</SPAN>[/TD]
[TD]Response Due</SPAN>[/TD]
[TD]Acknowledged</SPAN>[/TD]
[TD]Source</SPAN>[/TD]
[TD]Method</SPAN>[/TD]
[TD]# Qs</SPAN>[/TD]
[TD]Description</SPAN>[/TD]
[TD]Mid</SPAN>[/TD]
[TD]West</SPAN>[/TD]
[TD]NEE</SPAN>[/TD]
[TD]CP&R</SPAN>[/TD]
[TD]BB</SPAN>[/TD]
[TD]Thurrock</SPAN>[/TD]
[TD]Southend</SPAN>[/TD]
[TD]CSU</SPAN>[/TD]
[TD]CCG or CSU Service</SPAN>[/TD]
[TD]Sent to Service</SPAN>[/TD]
[TD]Services involved</SPAN>[/TD]
[TD]Clarification?</SPAN>[/TD]
[TD]Clarification Received</SPAN>[/TD]
[TD]Revised Dealine</SPAN>[/TD]
[TD]10 Day</SPAN>[/TD]
[TD]15 Day</SPAN>[/TD]
[TD]17 Day</SPAN>[/TD]
[TD]19 Day</SPAN>[/TD]
[TD]Overdue</SPAN>[/TD]
[TD]Response</SPAN>[/TD]
[/TR]
[TR]
[TD]-160</SPAN>[/TD]
[TD]1314381 [R1]</SPAN>[/TD]
[TD]01/04/14</SPAN>[/TD]
[TD]01/05/14</SPAN>[/TD]
[TD] [/TD]
[TD]Individual</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD] [/TD]
[TD]Telephony, Broadband & WAN - Internal Review</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-160</SPAN>[/TD]
[TD]F1415001</SPAN>[/TD]
[TD]01/04/14</SPAN>[/TD]
[TD]01/05/14</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD]Individual</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]8</SPAN>[/TD]
[TD]Mobile Phone Contracts</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]CSU</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Info Not Held</SPAN>[/TD]
[/TR]
[TR]
[TD]-159</SPAN>[/TD]
[TD]F1415002</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD]02/05/14</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]Media[/TD]
[TD]Email</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]Quality Premium Payment & Dementia</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]Both</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]MH, Liaisons</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-159</SPAN>[/TD]
[TD]F1415003</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD]02/05/14</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]Individual</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]IVF Funding</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CSU</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]IFR</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-158</SPAN>[/TD]
[TD]F1415004</SPAN>[/TD]
[TD]03/04/14</SPAN>[/TD]
[TD]06/05/14</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]MP</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]Named GPs for over 75's</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Part Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-167</SPAN>[/TD]
[TD]F1415005</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]18/04/14</SPAN>[/TD]
[TD] [/TD]
[TD]NHS England</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]18</SPAN>[/TD]
[TD]Telephony, Broadband & WAN</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD]CSU</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]ICT</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=8><COL><COL><COL><COL><COL span=2><COL span=5><COL></COLGROUP>[/TABLE]
Thanks a million in advance...[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
I have been fruitlessly searching for a way to do this, i've partially managed to work it but cannot get any further.
I have a spreadsheet where i log FoI requests. These have a 20 day time limit and we send reminders out after 10, 15, 17, 19 20 (due) & 21 (overdue) days. I am trying to create a list on a seperate tab of all those that are within their reminder period but haven't been closed or sent a reminder.
I have used the following formula:
[TABLE="width: 1160"]
<TBODY>[TR]
[TD]INDEX(RequestLog!B11:AC1008,MATCH(1,(RequestLog!AD11:AD1008="")*(RequestLog!Y11:Y1008="")*(RequestLog!B11:B1008<11)*(RequestLog!B11:B1008>5),0),2)
</SPAN>
This will show me the first instance of a reference needed a reminder at the 10 day point (i have managed to modify this for the other reminder points). I now want to combine this with the SMALL & ISERROR functions, copy it down, and produce a list.
Is anybody able to help me ? I have pasted below an extract from the main table (this shows column B through to AD, what is shown as a P should be in Wingdings and show a tick)
[TABLE="width: 1507"]
<TBODY>[TR]
[TD]Days remaining</SPAN>[/TD]
[TD]Ref</SPAN>[/TD]
[TD]Date
received</SPAN>[/TD]
[TD]Response Due</SPAN>[/TD]
[TD]Acknowledged</SPAN>[/TD]
[TD]Source</SPAN>[/TD]
[TD]Method</SPAN>[/TD]
[TD]# Qs</SPAN>[/TD]
[TD]Description</SPAN>[/TD]
[TD]Mid</SPAN>[/TD]
[TD]West</SPAN>[/TD]
[TD]NEE</SPAN>[/TD]
[TD]CP&R</SPAN>[/TD]
[TD]BB</SPAN>[/TD]
[TD]Thurrock</SPAN>[/TD]
[TD]Southend</SPAN>[/TD]
[TD]CSU</SPAN>[/TD]
[TD]CCG or CSU Service</SPAN>[/TD]
[TD]Sent to Service</SPAN>[/TD]
[TD]Services involved</SPAN>[/TD]
[TD]Clarification?</SPAN>[/TD]
[TD]Clarification Received</SPAN>[/TD]
[TD]Revised Dealine</SPAN>[/TD]
[TD]10 Day</SPAN>[/TD]
[TD]15 Day</SPAN>[/TD]
[TD]17 Day</SPAN>[/TD]
[TD]19 Day</SPAN>[/TD]
[TD]Overdue</SPAN>[/TD]
[TD]Response</SPAN>[/TD]
[/TR]
[TR]
[TD]-160</SPAN>[/TD]
[TD]1314381 [R1]</SPAN>[/TD]
[TD]01/04/14</SPAN>[/TD]
[TD]01/05/14</SPAN>[/TD]
[TD] [/TD]
[TD]Individual</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD] [/TD]
[TD]Telephony, Broadband & WAN - Internal Review</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-160</SPAN>[/TD]
[TD]F1415001</SPAN>[/TD]
[TD]01/04/14</SPAN>[/TD]
[TD]01/05/14</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD]Individual</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]8</SPAN>[/TD]
[TD]Mobile Phone Contracts</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]CSU</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Info Not Held</SPAN>[/TD]
[/TR]
[TR]
[TD]-159</SPAN>[/TD]
[TD]F1415002</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD]02/05/14</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]Media[/TD]
[TD]Email</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]Quality Premium Payment & Dementia</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD]Both</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]MH, Liaisons</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-159</SPAN>[/TD]
[TD]F1415003</SPAN>[/TD]
[TD]02/04/14</SPAN>[/TD]
[TD]02/05/14</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]Individual</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]IVF Funding</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CSU</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]IFR</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-158</SPAN>[/TD]
[TD]F1415004</SPAN>[/TD]
[TD]03/04/14</SPAN>[/TD]
[TD]06/05/14</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]MP</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]Named GPs for over 75's</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD]P</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Part Disclosed</SPAN>[/TD]
[/TR]
[TR]
[TD]-167</SPAN>[/TD]
[TD]F1415005</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]18/04/14</SPAN>[/TD]
[TD] [/TD]
[TD]NHS England</SPAN>[/TD]
[TD]Email</SPAN>[/TD]
[TD]18</SPAN>[/TD]
[TD]Telephony, Broadband & WAN</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]P</SPAN>[/TD]
[TD]CSU</SPAN>[/TD]
[TD]04/04/14</SPAN>[/TD]
[TD]ICT</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27/01/00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Disclosed</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=8><COL><COL><COL><COL><COL span=2><COL span=5><COL></COLGROUP>[/TABLE]
Thanks a million in advance...[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]