Finding "oldest outstanding" item

excelbeginner3

New Member
Joined
Nov 17, 2016
Messages
3
Hi all, I'm after some more help with my spreadsheet - this time with finding some of the oldest outstanding of particular items

The spreadsheet below is an exert from the Master. I need to show the oldest date of items yet to be completed which is displayed in a 'metrics' table on another worksheet.

For example, I want it to generate the oldest, yet to be completed QUOTE/SPEC REQUEST/ GENERAL ENQUIRY

Is this a V look up or something else?

Thanks in advance for any help!


Received ViaDate received in CCType of requestStatus Date completed
Direct email to CC 14/11/2016SPEC REQUESTComplete 14/11/2016
14/11/2016GENERAL ENQUIRYComplete
Customer14/11/2016GENERAL ENQUIRYComplete 14/11/2016
14/11/2016GENERAL ENQUIRYComplete 15/11/2016
Direct email to CC 14/11/2016QUOTE REQUSTIn progress
Direct email to CC 15/11/2016COMPLAINTIn progress
Direct email to CC 15/11/2016SPEC REQUESTWith Dean H
Online enquiry 16/11/2016QUOTE REQUSTComplete 16/11/2016
Ralf16/11/2016SPEC REQUESTComplete 17/11/2016
Marc16/11/2016SAMPLE REQUESTIn progress
Direct email to CC 16/11/2016COMPLAINTWith Quality
Direct email to CC 17/11/2016GENERAL ENQUIRYComplete

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

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi,

Check if this what you're after:

<b>Excel 2016 (Windows) 64 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Received Via</td><td style=";">Date received in CC</td><td style=";">Type of request</td><td style=";">Status</td><td style=";">Date completed</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Direct email to CC</td><td style="text-align: right;;">14-11-2016</td><td style=";">SPEC REQUEST</td><td style=";">Complete</td><td style="text-align: right;;">14-nov-16</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">14-nov-16</td><td style=";">GENERAL ENQUIRY</td><td style=";">Complete</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Customer</td><td style="text-align: right;;">14-11-2016</td><td style=";">GENERAL ENQUIRY</td><td style=";">Complete</td><td style="text-align: right;;">14-11-2016</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">14-nov-16</td><td style=";">GENERAL ENQUIRY</td><td style=";">Complete</td><td style="text-align: right;;">15-11-2016</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Direct email to CC</td><td style="text-align: right;;">14-11-2016</td><td style=";">QUOTE REQUST</td><td style=";">In progress</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Direct email to CC</td><td style="text-align: right;;">15-11-2016</td><td style=";">COMPLAINT</td><td style=";">In progress</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Direct email to CC</td><td style="text-align: right;;">15-11-2016</td><td style=";">SPEC REQUEST</td><td style=";">With Dean H</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Online enquiry</td><td style="text-align: right;;">16-11-2016</td><td style=";">QUOTE REQUST</td><td style=";">Complete</td><td style="text-align: right;;">16-11-2016</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Ralf</td><td style="text-align: right;;">16-11-2016</td><td style=";">SPEC REQUEST</td><td style=";">Complete</td><td style="text-align: right;;">17-11-2016</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Marc</td><td style="text-align: right;;">16-11-2016</td><td style=";">SAMPLE REQUEST</td><td style=";">In progress</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Direct email to CC</td><td style="text-align: right;;">16-11-2016</td><td style=";">COMPLAINT</td><td style=";">With Quality</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Direct email to CC</td><td style="text-align: right;;">17-11-2016</td><td style=";">GENERAL ENQUIRY</td><td style=";">Complete</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">QUOTE REQUST</td><td style="text-align: right;;">14-11-2016</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">SPEC REQUEST</td><td style="text-align: right;;">15-11-2016</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">GENERAL ENQUIRY</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B16</th><td style="text-align:left">{=IF(<font color="#0000FF">MIN(<font color="#FF0000">IF(<font color="#00FF00">(<font color="#800080">D2:D13<>"Complete"</font>)*(<font color="#800080">C2:C13=A16</font>),B2:B13,""</font>)</font>)=0,"",MIN(<font color="#FF0000">IF(<font color="#00FF00">(<font color="#800080">D2:D13<>"Complete"</font>)*(<font color="#800080">C2:C13=A16</font>),B2:B13,""</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B17</th><td style="text-align:left">{=IF(<font color="#0000FF">MIN(<font color="#FF0000">IF(<font color="#00FF00">(<font color="#800080">D2:D13<>"Complete"</font>)*(<font color="#800080">C2:C13=A17</font>),B2:B13,""</font>)</font>)=0,"",MIN(<font color="#FF0000">IF(<font color="#00FF00">(<font color="#800080">D2:D13<>"Complete"</font>)*(<font color="#800080">C2:C13=A17</font>),B2:B13,""</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B18</th><td style="text-align:left">{=IF(<font color="#0000FF">MIN(<font color="#FF0000">IF(<font color="#00FF00">(<font color="#800080">D2:D13<>"complete"</font>)*(<font color="#800080">C2:C13=A18</font>),B2:B13,""</font>)</font>)=0,"",MIN(<font color="#FF0000">IF(<font color="#00FF00">(<font color="#800080">D2:D13<>"complete"</font>)*(<font color="#800080">C2:C13=A18</font>),B2:B13,""</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

nb: beware of the typo in "quote requst"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

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
Top