Data extraction to another worksheet - date range and criteria problem

[Villalobos]

Hello,

I would like to ask some help regarding data extracting between two dates and filter criteria ignoring.

#1 problem for me: My code is not able to extract the data between two given dates.
#2 problem for me: If a filter criteria cell is empty the code should run forward and ignore this cell. It means that if just one filter criteria cell contain criteria then the code extract the data based on one criteria. If two filter criteria cells contain criteria then the code extract the data based on that two criterias and so on...

Could someone give me any advice how can this be done?


Thanks in advance!

 

[Smallman]

Villalobos

One of the problems you have with your data set up is that on your data sheet the date range is called something like Start Date and End date. While the date in your table is called Date. The advanced filter demands exact name matches from headers. So you need to call your start date "Date" and your end date "Date". Another problem with the dates is you have static dates in the start and end dates. You need to tell the Advanced filter that you want the dates to be between two points. Like so

=">="&YourStartDateCell

="<="&YourendDateCell

This will allow you the flexibility to put date criteria into your model. Also I believe the year month and week are not required as criteria as these will be covered off by the date criteria. I have removed these in the file. The file shows how this method can work and the following is the code you should be thinking of using for this sort of procedure. I have removed your named range as it was not global enough and added a fresh one originally titled Test.

Sub AdvFil()
    [B16].CurrentRegion.ClearContents
    Sheets("Data").Range("Table1[#All]").AdvancedFilter 2, Range("Test"), [B16]
End Sub

Hope this helps.

Take care

Smallman

 

[Villalobos]

Good Morning Smallman,

Thank you the advice, the #1 problem is solved, if I think about it more I agree with you if the start and finish date is given then the year/month/week is not required.
But how do you extract the data if only one criteria cell contain criteria? For instance I just would like to see the all record of plant P1 between 05.03.2013 - 22.04.2014.

Or how do you do if just two or three criteria cells contain criterias?

 

[Smallman]

Hi Villalobos

Do you mean you want a second criteria which says if the date is between those specified and if Plant = P1.

How does this relate to the first query?

Ta

Smallman

 

[Villalobos]

Yes, I would like this.
The data extraction would be optional (the criterias can be combined with each other), I mean as I change the criterias the extracted data change as well after the code run so the reporting could be much more dynamic. If a criteria cell is empty, then the code ignore it.

 

[Smallman]

Villalobos

You will need to think of something more inventive than having a blank cell and getting XL to ignore that with the Advanced filter. You will need to have a Wildcard instead of a blank cell. This means include everything and blank cells mean include the blank cells in that column.

I think if you put a wild card option in your criteria that might be a solution for you.

Take care

Smallman

 

[Villalobos]

Hi,

Thank you the direction!

 

[Smallman]

No worries Villalobos. Good luck with the rest of your project.

Smallman

 

[Villalobos]

I found an alternative way. I extended the data validation list with +1 blank cell and modified the filter cell with this formulae: =IF(Report!C5<>"";Report!C5;""). It is working!