Hi Bob, thanks for the suggestion, but I'm not sure I understand what an
"event monitor" is. Would you be able to explain? Is that a macro function?

Also, the pivot report isn't showing just a single month... it looks like
this:
2007 2008 2009
State1
Jan xxx.xx xxx.xx xxx.xx
Feb xxx.xx xxx.xx xxx.xx
Mar xxx.xx xxx.xx xxx.xx
... to December

State2
Jan xxx.xx xxx.xx xxx.xx
Feb... to December

I have five states. So, say the month is March. (That's the report I'm
working on right now.) I need to be able to report comparative monthly sales
by state (e.g., March of 2007, '08, '09), and YTD '07, '08, '09.

So, what I'm doing is summing Jan, Feb, & Mar... for '07, '08, & '09 FOR
EACH STATE (for the YTD report). I use that to create a table and a chart
for a dashboard. So I drag the SUM() formula for each year and each state
down one more row (15 times) (this is just one section of the dashboard).

I hope this clarifies...

"Bob Phillips" wrote:

If each pivot has the same field names and is to show the same single month,
you could put an event monitor on the selected date cell in the first pivot,
then change the other pivots to have the same values.

--
__________________________________
HTH

Bob

"Jeff" wrote in message
...
Hi, I have a sales report which needs to sum year-to-date sales by state.
Right now, the information is sitting in a pivot table linked to a live
database... so while my sales report may be for February, the pivot table
is
also reporting sales up to the current date (which is often a week or two
into the next month). That means I cannot use the "sales total" reported
in
the pivot table... because it will include sales for the current month
which
is not part of the report. So what I'm doing now, is linking a "table" to
the pivot report via the SUM() formula. Every month I drag the sum
formula
down one more row for EACH STATE! Is there not a way that I could have
the
argument of the SUM() function defined such that if the month is March,
then
the summed range is defined as the first cell in the range + the next two
cells below... e.g., I could have a table with each month equal to an
integer
(Jan, 0, Feb 1, Mar, 2, Apr, 3) and perhaps use VLOOKUP() to pull in the
correct integer by which to grow the range. This may be confusing. I'm
sorry if it is. If there is an entirely easier way to do this please let
me
know that as well.

AND the reason I'm not just going into the Pivot Table to restrict it to
the
current month is that there are 3 pivot tables per report and 4 reports
(one
for each sale representative... and growing)... and I don't want to have
to
go in and change each pivot table each month... This is also the first set
of
several reports that I'm working on... and I'm trying to make them as
automatic as I can.