Prev: Average If
Next: Determine "on time" status
From: Alex on 3 Jun 2010 14:01
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?


From: Tom-S on 3 Jun 2010 14:56
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?
>
>
 | 
Pages: 1
Prev: Average If
Next: Determine "on time" status