I'm trying to work out this problem, and wanted to see if others have
come up with a solution.

What I have is a pivot table report in Excel 2007, which shows a pay
period and amounts paid in overtime for each employee.  The list is
grouped by department.  Here is an example:

Dept         Name               OT Amount
200000     Smith, John        $1,900.00
              Jones, Jack         $525.00
              Busch, David       $1,275.00
200000 total                      $3,700.00

There are 9 non-exempt departments.  What I want to do is set up a
data bar that shows who gets paid most in overtime, to assist managers
in controlling overtime scheduling.  Here is the problem: two of the
departments have built-in (i.e. regularly scheduled) overtime, and so
they will normally have this.  Other departments, on the other hand,
should not be using any overtime at all - their amounts are generally
very low (like $50 per employee, and even less on average).

Therefore, I cannot apply the formatting rule to the entire pivot
table (that would negate the benefit of visual comparison for non-
overtime departments).  The alternative - doing it the old way - is
highlighting each range (for each department separately) instead of
using a pivot table rule.  One benefit of this is, of course, that I
can use different color bars for different departments.  However,
because staffing changes, this creates a lot of manual work, which
doesn't bother me but a pain for our Controller and CFO.

So... what I'm trying to do is:
1. Use a table-wide formatting rule for data bar
2. Base the "shortest bar" on a number generated by a formula along
the lines of GETPIVOTDATA("Min of OT Amount",$A
$4,"Department","200000")
3. Base the "longest bar" on a similar formula for maximum.

Right now I'm struggling with making this at least a semi-automated
process.  Anyone have ideas or suggestions?  Perhaps I should be using
a shortest/longest number value using a database function from the
source table instead?  Of course I could always generate a separate
report for each department, but at this point it's an academic
curiosity.