Searching a date range & returning multiple results

[Opinions]

Hi all,

I've been looking for a solution for the past couple of weeks but everything I find I can't seem to translate as it's close but not quite to my own issue.

The objective is to be able to search a date range on one sheet, and return all relevant matches on another.

The formula would be located on Sheet 1 and all data is on sheet 2 (as per attached file). When a search is done, Sheet 2 is interrogated and Sheet 1 shows the results.

For example:

07/01/2015FrankChange 48
08/01/2015BobChange 49
09/01/2015PaulChange 50
10/01/2015JeffChange 51

If I searched for results between 08/01/2015 and 10/01/2015, it would bring back the following results

08/01/2015 Bob Change 49
09/01/2015 Paul Change 50
10/01/2015 Jeff Change 51

My initial idea was to use a pivot table with a slider which works great for only one set like this, but I am going to be needing multiple tables which all need their own slider and it all gets a bit messy to look at.

Any ideas that you guys and gals could provide would be very much appreciated.

Thanks

 

[Pavan.Sada.PS]

Hello Sir..

Welcome to Chandoo.org and the world of excel

Request you to please have a look at the attached file and Let me know if attached file fulfil's your requirement..

Regards,
Pavan S

 

[prasaddn]

Hi,

Pls see if file works, the results are in descending order.

Regards,
Prasad DN

 

[prasaddn]

Hi,

I was not able to recall that small() is the opposite of large(), in the attached file of mine, replace in formula, Large to small.

You will get the result in ascending order

Regards,
Prasad DN

 

[Opinions]

Hi,

Pls see if file works, the results are in descending order.

Regards,
Prasad DN

Hi,

Thanks for the attached. It is almost pixel perfect for what I have been trying to do.

There is one rather interesting snag that I have hit when translating the formulas from the example sheet to the final one, it seems to suddenly be offsetting the dates it returns by 2 days.

If I search for results between 10/01/2015 and 20/01/2015, it brings back matches between 12/01/2015 and 22/01/2015.

I've named the columns in similar ways and they contain no white space. Also I'm assuming that the DATES in the formula are reference to the names cells rather than a function in Excel. Any ideas what might be causing it?

Thanks again!

 

[prasaddn]

Hi,

Yes, DATES in the formula are names and not functions.

Is it possible to upload the file? could be something to do with format of dates.

regards,
Prasad DN

 

[Opinions]

Hi,

Yes, DATES in the formula are names and not functions.

Is it possible to upload the file? could be something to do with format of dates.

regards,
Prasad DN

Hi Prasad,

Thank you for taking the time to help with this.

Here is the file. I have stripped out all of the non-essential sheets and left only those that are relevant.

Thanks again.

Paul

 

[NARAYANK991]

Hi Paul ,

Never use the original construct which was in your file ; use the generic construct which is there now.

Narayan