I have been having serious trouble trying to sort this out. It is a little
complicated and I will score major points with my supervisor if I can sort
this out so here goes...

I have four sheets of data that represent various international billing
disputes. They all contain the date that the dispute was filed in column B
and the issue type, which is one of 14 options, in column D. I would like to
summarize the data in a table on a fifth sheet.

The table needs to be organized by columns representing each month's
disputes, and rows identifying the type of billing dispute. So, for example,
there is a March 2009 column which contains all March 09 disputes from the 4
spreadsheets. Also, theres a row so we can see all Misquotes (an issue type)
from the entire timeframe we have recorded data. This way we can pinpoint the
number of a particular type of issue in any given month.

At present, I am able to pull information from two spreadsheets into the
first cell (C7) using the formula:

=SUM(--('CLOSED INTERNATIONAL DATA'!$B$2:$B$1551<=C$3),--('CLOSED
INTERNATIONAL DATA'!$B$2:$B$1551=C$2),--('CLOSED INTERNATIONAL
DATA'!$D$2:$D$1551=$B8), --('International
Data'!$B$2:$B$1551<=C$3),--('International
Data'!$B$2:$B$1551=C$2),--('International Data'!$D$2:$D$1551=$B8))

Where CLOSED INTERNATIONAL DATA and International Data are two of the four
other sheets; C2 and C3 are the beginning dates of the month respectively
that is represented in column C; and B8 is the cell that indicates the issue
type.

The problem arises when I try to add the components from the third sheet to
the formula in cell C7 (and every subsequent cell). Is there a limitation on
foreign sheet references in one formula? Does anyone know any way to
circumvent this obstacle?

Assuming the data all lies within the same year, say on your 5th sheet you
have some column headers in row 1: A1 is Issue Type, B1 is Jan-2009, C1 is
Feb-2009, etc up to M1 is Dec-2009 (with B1 to M1 formatted as mmm-yyyy).
Then A3:A16 is filled with your 14 issue types.

You will then fill a table B3:M16 with formulas to calculate the number of
issue types raised in each month. In cell B3 is the formula:

=SUMPRODUCT((MONTH('Sheet 1'!$B$2:$B$1551)=MONTH(B$2))*('Sheet
1'!$C$2:$C$1551=$A3)) + SUMPRODUCT((MONTH('Sheet
2'!$B$2:$B$1551)=MONTH(B$2))*('Sheet 2'!$C$2:$C$1551=$A3)) +
SUMPRODUCT((MONTH('Sheet 3'!$B$2:$B$1551)=MONTH(B$2))*('Sheet
3'!$C$2:$C$1551=$A3)) + SUMPRODUCT((MONTH('Sheet
4'!$B$2:$B$1551)=MONTH(B$2))*('Sheet 4'!$C$2:$C$1551=$A3))

Drag fill the formula to the rest of the table and it should collate your
year's data.

Gets a bit more complicated if data lies across multiple years - post again
if it does.

Regards,

Tom


"Alex" wrote:

I have been having serious trouble trying to sort this out. It is a little
complicated and I will score major points with my supervisor if I can sort
this out so here goes...

I have four sheets of data that represent various international billing
disputes. They all contain the date that the dispute was filed in column B
and the issue type, which is one of 14 options, in column D. I would like to
summarize the data in a table on a fifth sheet.

The table needs to be organized by columns representing each month's
disputes, and rows identifying the type of billing dispute. So, for example,
there is a March 2009 column which contains all March 09 disputes from the 4
spreadsheets. Also, theres a row so we can see all Misquotes (an issue type)
from the entire timeframe we have recorded data. This way we can pinpoint the
number of a particular type of issue in any given month.

At present, I am able to pull information from two spreadsheets into the
first cell (C7) using the formula:

=SUM(--('CLOSED INTERNATIONAL DATA'!$B$2:$B$1551<=C$3),--('CLOSED
INTERNATIONAL DATA'!$B$2:$B$1551=C$2),--('CLOSED INTERNATIONAL
DATA'!$D$2:$D$1551=$B8), --('International
Data'!$B$2:$B$1551<=C$3),--('International
Data'!$B$2:$B$1551=C$2),--('International Data'!$D$2:$D$1551=$B8))

Where CLOSED INTERNATIONAL DATA and International Data are two of the four
other sheets; C2 and C3 are the beginning dates of the month respectively
that is represented in column C; and B8 is the cell that indicates the issue
type.

The problem arises when I try to add the components from the third sheet to
the formula in cell C7 (and every subsequent cell). Is there a limitation on
foreign sheet references in one formula? Does anyone know any way to
circumvent this obstacle?