A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Challenging Formula Issue



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 07:01 PM posted to microsoft.public.excel.worksheet.functions
ALEX
external usenet poster
 
Posts: 731
Default 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  
Old June 3rd, 2010, 07:56 PM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.