Conditional Formatting on Pivot Table row labels

[ganeshmurthy]

Hi All,

I have a pivot table which has powerpivot as source. I dragged a field under row label and need to set a conditional formatting. It doesnt work. If i copy the powerpivot data to excel sheet and make it as source the conditional formatting under row label works. Any clues why it doesnt work if the power pivot is source.

To put in simple, conditional formatting (Excel Icon Sets) for the field under Row Label NOT under values.

Thanks in advance.

Best Regards,
Ganesh

[dilipandey]

Hi Ganesh,

Welcome to the forum.
The question is , you need to do the conditional formatting on a row of pivot table.. correct? As per my knowledge, in this case it does not matter what is the source of pivot as after getting the data in pivot, it's the pivot where the conditional formatting need to be applied, please upload a sample. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[ganeshmurthy]

Hi Dilip,

Please find attached a sample.

In srcFromPowerPivot sheet cell A is from powerpivot under row label comparing the dates in cell C (3 dates) and the condtional formatting doesnt work. In cell J it worked cos I dragged under value instead of row label. In the srcFromWorksheet it worked even though it is under rowlabel.

Sheet3 is just a copy of powerpivot data. I copied manually from powerpivot and pasted in sheet3.

Please let me know if there is any query that you have.

Thanks for the help.

Best Regards,
Ganesh

[dilipandey]

Hi Ganesh,

In srcFromPowerPivot sheet cell A is from powerpivot under row label comparing the dates in cell C (3 dates)

In sheet "srcFromPowerPivot", in cell A?
What should come in place of ?... are you talking about A1, A5, A9 .. or what ? Same issue is with rest of the description.. I need clear address to enable me to look into correct place.

You description is not clear to me.. but as an alternative, I would suggest you to check if your date is actually a DATE and not a text which may be appearing as Date

Regards,
DILIPandey
<click on below * if this helps>

[ganeshmurthy]

Hi Dilip,

The date is a "date" and not a text.

What I mean is each cell in A should be compared with the 3 dates in E and should do the conditional formatting (excel icon sets) accordingly.

If you see the cell A in srcFromWorkSheet you know what I mean.

Please let me know if you have any queries.

Thanks and Best Regards,
Ganesh

[dilipandey]

Yes Ganesh.. I have queries.

The date is a "date" and not a text.

I guess you are saying this for column A of sheet "SrcFromWorkSheet".

What I mean is each cell in A should be compared with the 3 dates in E

I believe you are saying "each cell in Column A should be compared with the 3 dates in column E"... but my friend, there are no dates in Column E .. Then to verify further, I checked all the sheets of workbook and I did not found any date in column E.

I can assume some things here and can try to provide some solution but that may not be what you are be looking for. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[ganeshmurthy]

Hi Dilip,

The cell A (date) in srFromPowerPivot is general BUT i have checked modifying it in to date. I couldnt do the conditional formatting with icon sets. In the sheet SrcFromPowerPivot E2, E3, E4 are dates.

Thanks and Best Regards,
Ganesh

[dilipandey]

HI Ganesh..

In the sheet SrcFromPowerPivot E2, E3, E4 are dates.

These cells are appearing as BLANKS to me.. are we looking at same workbook ? thanks.

Regards,
DILIPandey
<click on below * if this helps>

[ganeshmurthy]

Hi Dilip,

Sorry, I was away for few days and couldnt reply to you. Nopes, we were not on the same workbook :-( But you can try having 3 different dates in column E.

Rest all are same in the file except this.

Btw, do you have any clues on how to filter a slicer based on other slicer's selection item?

For eg. I have account as slicer1 and CFR as slicer2. CFR A has Account: A1, A2, A3, A4. If I slice on CFR slicer by selecting A then I see all ccounts related to that CFR i.e., A1, A2, A3, A4, which is expected. If I slice on Account and click on A1, currently I am seeing A1 only which is as per the normal scenario. But I want to see again A1, A2, A3, A4 even if I click on Account A1 since A1 is linked to CFR A and CFR A has 4 accounts. Other than the slicer, I have Account No, CFR No filter also.

Any clues on how to do it?

The below code works if I run manually but If I assign it to a slicer it doesnt work.

Debug.Print "FilterData"

ActiveSheet.PivotTables("Overview").ManualUpdate = True

Dim Cache As Excel.SlicerCache
Dim ItemName As String
Dim sItem As Excel.SlicerItem

Set Cache = ActiveWorkbook.SlicerCaches("Slicer_Account_Fltr111")

For Each sItem In Cache.SlicerItems
ItemName = sItem.Name

If (sItem.Selected = False) Then ActiveSheet.PivotTables("Overview").PivotFields("Account No").PivotItems(ItemName).Visible = False


Next sItem

Set Cache = ActiveWorkbook.SlicerCaches("Slicer_CFR_Fltr111")

ActiveWorkbook.SlicerCaches("Slicer_CFR_Fltr111").ClearManualFilter

For Each sItem In Cache.SlicerItems
ItemName = sItem.Name

If (sItem.HasData = False) Then ActiveSheet.PivotTables("ActivitiesOverview").PivotFields("CFR No").PivotItems(ItemName).Visible = False

Next sItem

Debug.Print "asaas"
ActiveSheet.PivotTables("Overview").PivotFields("Account No").ClearAllFilters
ActiveSheet.PivotTables("Overview").PivotFields("Account No").EnableMultiplePageItems = True

ActiveSheet.PivotTables("Overview").ManualUpdate = False

Thanks in advance for the help.

[ganeshmurthy]

Hi Dilip,

Sorry, I was away for few days and couldnt reply to you. Nopes, we were not on the same workbook :-( But you can try having 3 different dates in column E.

Rest all are same in the file except this.

Btw, do you have any clues on how to filter a slicer based on other slicer's selection item?

For eg. I have account as slicer1 and CFR as slicer2. CFR A has Account: A1, A2, A3, A4. If I slice on CFR slicer by selecting A then I see all ccounts related to that CFR i.e., A1, A2, A3, A4, which is expected. If I slice on Account and click on A1, currently I am seeing A1 only which is as per the normal scenario. But I want to see again A1, A2, A3, A4 even if I click on Account A1 since A1 is linked to CFR A and CFR A has 4 accounts. Other than the slicer, I have Account No, CFR No filter also.

Any clues on how to do it?

The below code works if I run manually but If I assign it to a slicer it doesnt work.

Debug.Print "FilterData"

ActiveSheet.PivotTables("Overview").ManualUpdate = True

Dim Cache As Excel.SlicerCache
Dim ItemName As String
Dim sItem As Excel.SlicerItem

Set Cache = ActiveWorkbook.SlicerCaches("Slicer_Account_Fltr111")

For Each sItem In Cache.SlicerItems
ItemName = sItem.Name

If (sItem.Selected = False) Then ActiveSheet.PivotTables("Overview").PivotFields("Account No").PivotItems(ItemName).Visible = False


Next sItem

Set Cache = ActiveWorkbook.SlicerCaches("Slicer_CFR_Fltr111")

ActiveWorkbook.SlicerCaches("Slicer_CFR_Fltr111").ClearManualFilter

For Each sItem In Cache.SlicerItems
ItemName = sItem.Name

If (sItem.HasData = False) Then ActiveSheet.PivotTables("ActivitiesOverview").PivotFields("CFR No").PivotItems(ItemName).Visible = False

Next sItem

Debug.Print "asaas"
ActiveSheet.PivotTables("Overview").PivotFields("Account No").ClearAllFilters
ActiveSheet.PivotTables("Overview").PivotFields("Account No").EnableMultiplePageItems = True

ActiveSheet.PivotTables("Overview").ManualUpdate = False

Thanks in advance for the help.