locked
pivot table filter and if then statement RRS feed

  • Question

  • Hi there.

    I need some help on a if then statement.

    It is possible to use the filter on a pivot table with an if then statement

    month information is selected in pivot table filter ie jan, feb and march in cell d8

    i would like to use if d8 equal jan,feb and march then q1 in cell f8 but i get an error using d8.   does anyone have the right instruction that it recognize the pivot table.

    I hope this explain.

    thanks for your help.

    Keith

    Wednesday, March 23, 2016 2:18 PM

Answers

  • Hi Keith,

    >> pivot table filter and if then statement

    Could you share us more details information? In my option, we could set filter with vba. Here is a simple code:

        ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME").ClearAllFilters
        ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME").PivotFilters.Add2 _
            Type:=xlCaptionContains, Value1:="1"

    If you want to filter the pivot table with an if then statement, I think you could use if statement before above code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, March 25, 2016 6:25 AM

All replies

  • Hi Keith,

    Based on your description, could you provide the code about your problem?

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Thursday, March 24, 2016 11:44 AM
  • Hi Keith,

    >> pivot table filter and if then statement

    Could you share us more details information? In my option, we could set filter with vba. Here is a simple code:

        ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME").ClearAllFilters
        ActiveSheet.PivotTables("PivotTable1").PivotFields("NAME").PivotFilters.Add2 _
            Type:=xlCaptionContains, Value1:="1"

    If you want to filter the pivot table with an if then statement, I think you could use if statement before above code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, March 25, 2016 6:25 AM