Rank Error in Pivot Table
 

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?

Rank Error in Pivot Table
 

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" wrote:

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?

Rank Error in Pivot Table
 

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.


"Mike H" wrote:

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" wrote:

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?