Prev: Average IfNext: 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 IfNext: Determine "on time" status