Hi Andrew,

Just now read your post. I see the problem. The formula needs the pivot

table data to start in row 5 for the INDIRECT function with its COUNTA

argument to work. If you have a bunch of report filter/page filter items that

cause the pivot table data to start at, say, row 7 instead of row 5, then

change the formula so that where it now says +3 for the INDIRECT functions to

say +5. See before and after formulas, below.

Before: =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+3),MATCH("*

total",A5:INDIRECT("A"&COUNTA(C:C)+3),0),3)

After: =C5/INDEX(A5:INDIRECT("C"&COUNTA(C:C)+5),MATCH("*

total",A5:INDIRECT("A"&COUNTA(C:C)+5),0),3)

My first solution was simpler, but less elegant:

=C5 / INDEX(A5:C$10000,MATCH("* total",A5:A$10000,0),3)

This works great as long as your pivot table doesn't go beyond 10,000 rows.

This seems a sloppy way to deal with the uncertainty of the number of rows in

the pivot table, so I prefer the solution that uses the INDIRECT function to

mark the end of the MATCH range.

Let us know how you solve your problemâ€¦