If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Challenging Formula Issue
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? |
#2
|
|||
|
|||
Challenging Formula Issue
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? |
Thread Tools | |
Display Modes | |
|
|