Produce a "To do..." list using Index/Match/Small functions...

I

IainG84

New Member
Joined
Oct 2, 2013
Messages
4
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]
 
Sort by date Sort by votes
I've still been trying to do this, but really cannot make it work. Just bumping in the hope someone can help.... Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
V
  • Question Question
time and date fix
Replies
7
Views
217
vmjan02
V
M
  • Question Question
Dynamic Formula
Replies
2
Views
334
Mengli
M
L
  • Question Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
519
Lukma
L
K
  • Solved
macro or formulas to lookup by dates
2
Replies
13
Views
238
kelvin_9
K

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top