Lookup using date and criteria

[ChrisRocks]

Hi there,

I need to find all the users who requested a transfer to support. If they did request a transfer, I need their call duration based off a date.Is there a formula what can do this? Below is a sample of the date. For example, if I selected 8/9/2011, I would need a listing of:
78
78
42


8/9/2011 10:34
8/9/2011 10:38 78 ANSWER
8/9/2011 11:03 78 ANSWER
8/9/2011 11:55 42 ANSWER
8/10/2011 12:01 24 ANSWER
8/10/2011 12:01
8/10/2011 12:01 CANCEL
8/11/2011 12:01
8/11/2011 11:26 CANCEL

Thanks in advance.

CR

[Colin Legg]

Hi Chris,

This is possible to do with formulas, but the best way to get the list would be using the advanced filter. Would using the advanced filter be acceptable - if so, I can mock up a sample file for you?

[ChrisRocks]

Yes please Colin

[Colin Legg]

Okay, I've attached an example for you.

Type the date into F2 (I've already entered 9th August 2011).

Then Data > Filter > Advanced Filter

• Copy to another location
• List Range $A$1:$C$10
• Criteria Range $G$1:$I$2
• Copy To $L$1

The answer 78,78,42 will be automatically placed in L2:L4.
If you want me to explain the criteria range I set up then let me know...

[ChrisRocks]

Thanks Colin. Works great, but I was hoping for something less labour intensive.

CR

[Colin Legg]

It can be automated with VBA if you want...