Highlights cells in sheets created after pivot filter automation based on condition (title edited)

[nitesh]

Hello

I have to prepare an aging report weekly which is prepared by extracting data in a master sheet and creating a Zone wise Sheets from using Pivot Filter. How can I highlight the name of Customers in every sheets whose outstanding is above 45 days automatically.
I tried with conditional formatting in Pivot sheet and it was a success but when I tried the same formula in others there was no effect.Please suggest the correction or any other method.

 

[Luke M]

It looks like your workbook has 1 data sheet, and all the rest are the same PivotTable, just filtered to different items. Your conditional formatting formula should be:
=GETPIVOTDATA("46 To 60 day",PIVOT!$A$3,"Customer Name",C5)>0

The important part is in red. This needs to be the name of the cell you are applying it to. So, if you select range D4:D1000 and go to apply a CF, the formula would be:
=GETPIVOTDATA("46 To 60 day",PIVOT!$A$3,"Customer Name",D4)>0

 

[nitesh]

Thanks for Your Reply Luke but the CF is not working when I am applying it to Filtered pages.It is only working for the Pivot Sheet.I wanted CF for all my sheets.Please suggest.

 

[NARAYANK991]

Hi Nitesh ,

I am not able to understand what your problem is ; if I select the range C5 through C15 on the tab labelled RURAL KAMRUP , and enter the following rule :

=AND(OR($O5>0,$P5>0),ROW()>4)

I get the customers highlighted. What is your problem ?

Narayan

 

[nitesh]

Thanks Narayan. I tried the code you mentioned and its working but what if I want to skip those blank cells between the Customer Name where Pivot Total is applied and also I have to insert the CF for all the sheets separately. Can I just apply the CF to Pivot and when I filter automatically the same settings get applied to all the sheets.

 

[NARAYANK991]

Hi Nitesh ,

I do not understand how this is possible ; if you specify some CF rule on one worksheet , how do you expect it to take effect on some other worksheet ? If you wish to have CF on all the sheets , you have to specify and apply the rules on each and every individual worksheet.

Narayan

 

[nitesh]

I understand your point but I am preparing one Pivot Sheet and then using Pivot Report Filter other Sheets are generated. So can't I apply the CF or highlight settings to my Master Pivot and then when I run Report Filter, other sheets gets created with the same settings.

 

[NARAYANK991]

Hi Nitesh ,

Check the file now.

What I have done is applied the CF to the sheet labelled PIVOT without any pivot filter. Thereafter , I apply the filter and select LOWER ASSAM ; once the filter has been applied , I make a copy of the sheet ; this copy has the same CF rules ; I rename this sheet to LOWER ASSAM. Repeat this for all the criteria.

Narayan

 

[nitesh]

Thanks Narayan for the effort but I have to generate multiple sheets which I do by selecting the Pivot Table in Pivot Sheet,go to options and click on "Show Report Filter Pages" rather than making a zonewise copy of sheets.Following this process the CF of original sheets are not applied on Filtered Sheet.

 

[NARAYANK991]

Hi Nitesh ,

I do not know , but as far as my knowledge goes , the CF rules are attached to a worksheet ; when you generate the individual filter pages , you are creating new worksheets ; by following the steps you have mentioned , this process becomes very easy. The flip side is that now you have to take the effort to introduce all the CF rules on each and every sheet manually.

If as I have posted , you apply the filter one by one , it is a painstaking method , but as far as the CF are concerned , they are already present on every sheet , since every sheet is a copy of the main pivot sheet.

You can take your pick - either create the sheets easily , and introduce the CF rules with some effort or create the sheets with some difficulty and your CF rules don't need any effort.

The creation of sheets can be more easily automated using VBA , than the introduction of the CF rules ; all that is needed is to create a copy and rename it to whatever is the text in cell B1 of the sheet.

Narayan

 

[nitesh]

Thanks Narayan for your feedback and even I opine the same.After creating Report filter pages perhaps I could create a macro to apply CF on individual sheet but tell me whether any VB code can ease all this effort?

 

[NARAYANK991]

Hi Nitesh ,

I can only repeat what I said in my earlier post :

The creation of sheets can be more easily automated using VBA , than the introduction of the CF rules ; all that is needed is to create a copy and rename it to whatever is the text in cell B1 of the sheet.

Narayan