Rank Error in Pivot Table

[Supe]

I have a pivot table set up where you can select a time period and a
geography from a drop down list. The first two columns of my report rank
items by dollar by the geography dollars(column E) and the geographies
competitors dollars(column M). If I change the geogrphy's using the drop
down the data changes and the Rank columns work fine. When I change the time
period, my rank columns do not work and just displays a #DIV/0! The remaning
columns all update accordingly, just the rank columns don't work. Formula
used in the rank columns are below.

=IF(C10<>"",RANK(E10,$E$10:$E$2000),"")
=IF(C10<>"",RANK(M10,$M$10:$M$2000),"")


Any idea as to why it would work when changing the time period?

 

[Mike H]

Hi,

You need VB for that and this assumes the colours are a result of shading
and not conditional formats which is much more difficult. ALT+F11 to open VB
editor. Right click 'ThisWorkbook' and insert module and paste the code below
in,

call with

=SumByColor(A1:A29,B1)

Where A1:A29 is the range to sum abnd B1 is shaded with the colour you want
to sum


Function SumByColor(Rng As Range, ClrRange As Range) As Double
Dim c As Range, TempSum As Double, ColorIndex As Integer
Application.Volatile
ColorIndex = ClrRange.Interior.ColorIndex
TempSum = 0
On Error Resume Next
For Each c In Rng.Cells
If c.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + c.Value
End If
Next c
On Error GoTo 0
Set c = Nothing
SumByColor = TempSum
End Function

Mike

 

[Supe]

Mike,

Think you may have responded to the wrong question. My issue had nothing to
do with colors. Was a error in a ranking formula in a pivot table.