Bob, thanks a lot.
Daniel
"Barb" wrote:
Hi Daniel
How about if you had one sheet with all that data plus one more column for
"State".Then you can just run a pivot table against it. It would give you
much more flexibility with any reports.
Barb
"Daniel Bonallack" wrote:
I have 50 worksheets (one for each US state). Each has three columns -
salesperson, year and sales.
On a new tab, I have in A2:A51 the 50 states. In cells B1:E1 I have the
years 2001, 2002, 2003, 2004.
I want to know the sales, per year for John Doe, in each of the states. I
have range names set up in each of the 50 tabs (done with a macro).
In cell B2 I have the formula =SUMPRODUCT((rPersonAlaska="John
Doe")*(rYearAlaska=B$1)*rSalesAlaska)
This of course works for Alaska, but how can I make the formula cleverly
refer to the state names in column A?
Sorry this is complicated - thanks for any help
Daniel
|