Change pivot page filter based upon two cell values (dates from and to)

[Victor70]

Dear experts,

I saw solutions to update the pivot page filter based upon a cell value via a macro (see the link).
http://www.excelforum.com/excel-prog...ell-value.html

Is there a way to apply a from-to criteria for the pivot page filter to get the data for specific time interval? I was unable to modify the referenced code being unsure if pivot page filter supports complex criteria at all.

Thanks a lot for any thoughts.

This duplicates the same question at http://www.excelforum.com/excel-gen...d-upon-two-cell-values-dates-from-and-to.html No responses there so far...

 

[arvindkrkaushik]

Good Morning Jerry,

The data I sent you, I have deleted some rows due to size limitations.. so all blank rows removed for "Joined On1" data, Please try another field which has blank rows (blank dates) like "HAT Scheduled1" please add a pivot for this field and then run this macro...belive me this is not working. When I run the macro after deleting blanks dates for each stage (Created Date1, Joined On1, HAT Scheduled1 etc..) this macro is working fine. So I request you to please take more pain for me and run macro for field which has blank dates...


Many thanks once again...


Thanks,
Arvind Kaushik

 

[arvindkrkaushik]

Jerry,

This is showing following error in "HAT Test Schedule1" Pivot:
Error 2042
Type mismatch

I don't know why this error is occuring...Please resolve this.

Thanks,
Arvind

 

[Jerry Sullivan]

Hi Arvind,

Having the blanks mixed with the dates is probably the reason that the pivotfield returns an error when its pivotitem's visible state is read. Two work arounds to consider:

1. Display your source data with Number formating instead of dates (this makes it harder to use the PivotTable filters without the dashboard).

2. Add a 0 value instead of blanks when there is no data. This will display as 01-01-1900 which you could ignore, or use a Conditional Formatting Rule to display as "-" or " ".

I was able to get both these methods to work on your file, although I had to rename the PivotField to "clear" the mixed formatting that the PivotTable seems to retain even after Refreshing.

 

[arvindkrkaushik]

Thanks a lot Jerry for your help!!!!...Its working fine now for me as well. But there is one more problem when I add this '0' Zero or 1-1-1900 this in increasing my file size..Now suggest how to reduce this size...Or there may be other way to do it without adding 1-1-1900.



Thanks,
Arvind

 

[Jerry Sullivan]

The first file that you sent me was very large at over at 15 MB. The addition of these zeros might add some file size, but it will be small in relation to your file. You only need to do this for the columns that have dates that will be filtered in the Pivots.

There are probably other opportunities to reduce your files size more significantly. There are many threads on this on the Web, so I won't try to suggest all the things to try, but instead encourage you to research that.

One thing to do that might not be listed in the advice you find is have all your PivotTables that have the same data source share the same Pivot Cache. The easiest way to do that is to make one PivotTable, then copy it and modify it several times instead of creating separate PivotTables .

 

[arvindkrkaushik]

Thanks a lot Jerry for this valuable information & all the help you provided.

Thank you once again.

Arvind Kuahik