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?


0
Utf
6/3/2010 6:01:27 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
934 Views

Similar Articles

[PageSpeed] 45

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?
> 
> 
0
Utf
6/3/2010 6:56:50 PM
Reply:

Similar Artilces:

report sorting formula
Hi everyone - awhile back i posted on here a question about sorting a report in a "weird" way. i have a slightly different question now... i have a report that is sorted by a purchase order number (ponum) from most recent to oldest. currently we have ponums from 1-2999, but are starting to get ponums of 3000+. this is the formula: =IIf(CInt(Left([po_number],2))>=30,"19" & [po_number],"20" & [po_number]) it works to sort from 2999 (most recent) - oldest perfectly, except now i have to add in the 3000's on top on the report thanks Hi It may se...

Use of array formula
I would like to use the worksheetfunction.linEst function in excel VBA, but need help on how to identify that the function returns an array rather than a single value. Thank you. -- Ho-Shu By selecting several cells first, like 5 rows x 2 columns, for example. Enter the LINEST function, press ctrl/shift/enter, see different results in the cells. There's not a way to inherently know if a function should be entered as an array formula without using help (excel's or other folks' or newsgroups, or trial & error --) HTH Bob Umlas Excel MVP "hsPipe"...

Exchange 2003 hotfix...OWA attachment issue
I need to know if the Exchange 2003 SP1 included the hotfix 838236 Post-RTM Rollup. According to the KB article it is included in the SP1 update, but if you look at the SP1 bug fixes, it never mentions this issue or the KB article. The issue is one others are also having, dealing with attachments not visible in OWA. From the sounds of the KB article 827637 it is the answer to this issue for us. But before we install it, we want to verify whether or not it was actually part of SP1 (which is already installed on our Exchange 2003server). Also, if anyone else has this problem and ca...

Formula #67
I have a sheet that is used to post daily mileage and expenses for an employee. He works for a property management company, so he's traveling during the day from one property to the next, and each property has a two-digit code assigned to it. Easy enough to sum things up on the daily log; however, then I want to create a second consolidated sheet showing the totals for each one of those locations he travels to during the month. So, for example, I have the following columns in the sheet: Date Description of Travel or Expense Property Miles Other Expenses Code Some codes are CC, H...

Checkbook issue
I want to know how I can pay a vendor without it affecting the checkbook. I have an account that simply reduces a liability, and my G/L accounts are okay, however the amounts appear as o/s cheques in the checkbook? Therefore the cash account and the checkbook are out of balance. Any way around this? Using Dymamics GP 9.0 It sounds like you will first have to reconcile your checkbook to bring the balance to the correct amount. You can always create an adjustment as an in/out to the cash account having no impact on your GL. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Globa...

How to write a formula
Hi, I have a column that lists the names of pieces of equipment. These are not sorted in any order in this column (the sheet is sorted by the owner's name in another column). It includes, for example A5 C1 A3 B2 A4 A1 C3 etc Further down in this column I have the next available number for A, B & C. To date I have been manually updating this but I am sure there must be a way to write a formula that searches the column above and gives me the next number. Any suggestions? Cheers Henry -- henry ------------------------------------------------------------------------ henry's Prof...

Payroll Split Challenge
We have a unique situation for distribution of Payroll and a one benefit expense. Example: Employee John Doe is full time salaried employee and receives $2000 per pay period. The employee wages need to be slit 50% to each of the following Accounts Location Dept Acct 01 01 5000 Wages 50% = $1000 02 01 5000 Wages 50% = $1000 They have 1 benefit expense which is based on a percentage of gross – 25% or $500 for our example. They also want the benefit expense to be split – to a single or multiple GL code...

Formula PDA
Can somebody tell me what if this formula has alternitives because this doesn't work on my PDA (pocket excel). =SUMPRODUCT((sheet1!$A$2:$A$132=2)*(sheet1! $C$2:$C$132="J")) Does it support array formula? If so, perhaps =SUM(IF(sheet1!$A$2:$A$132=2,IF(sheet1!$C$2:$C$132="J",1,0),0)) -- HTH RP (remove nothere from the email address if mailing direct) "marciemar" <anonymous@discussions.microsoft.com> wrote in message news:01b901c529a9$e37366d0$a501280a@phx.gbl... > Can somebody tell me what if this formula has > alternitives because this d...

formula if with condition in other cell
Morning, Is it possible to do an if formula in which the condition applies the true value if the value is another cell? Example =IF(a1<>"F",1,0). I mean if <>"F" is in another cell and not in the formula. Happy New Year to all Can you try it. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "1234" <altachicasaudaces@gmail.com> wrote in message news:e1365b76-6792-4752-8ca7-9694a48fe218@r5g2000yqb.googlegroups.com... > Morning, > > Is it possible to do an if formula in which the condi...

Focus issue 02-26-08
I have a listbox that is made visible when a user clicks on an associated command button. The listbox includes an option to "CANCEL" if they decide not to select one of the options listed. If the user ignores the "CANCEL" option but instead clicks somewhere else on the form, I have coded in the "LostFocus" event a statement to again to make "False" the listbox's visibility. I get a runtime error that I can't make the visibility false while the control has the focus. How can that be if the "LostFocus" event has fired? Bill That'...

how to make Under invoice formula
Please help me how to make formula if as following case? Thank you very much in advance to expert helpers. I want divide a total amount of many diffrent product, in according to there actual value of product of each product. as following example. Actual invoice and value & The actual amount Product quantity unit price total price Bag 1000 2.1 $2,100 shoes 5000 3.1 $15,500 blanket 8000 4.32 $34,560 jacket 1500 1.8 $2,700 bed sheet 6000 ...

IRERR nested formula help please
hi just wondering if someone can help with the following nested ISERR formula. I want to be able to show in J3 that if the value in G3 is "0" then show as "-100%" OR if the value is "NULL" then show as "0" but if the value is >0 then I3/G3*100 it is working to a degree but if value in G3 is NULL then still shows as -100% I want it to show as 0% if NULL and -100% if 0 - hope it makes sense.... currently have following in J3 =IF(ISERR(I3/G3),-100,I3/G3*100) G3 = Order I3 = Profit thanks in advance Depending on how I interpret NULL =IF(ISBLANK(G3)...

no cell results from formula
Often, in Microsoft Excel 2003, when I put a formula in a cell th results do not show in the cell. For example, today it was a simpl +b6-b7; Each of these cells had numbers in them - 15,000 and 10,00 respectively, yet the cell showed zero. Automatic calc was on. tried that same formula in several cells and they all showed zero. Ca anyone help -- Abra ----------------------------------------------------------------------- Abram's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1524 View this thread: http://www.excelforum.com/showthread.php?threadid=26871 Pe...

formulas
What formula do you use to change a birthdate to find the age of person Michelle If you had the birthday in A1 =DATEDIF(A1,TODAY(),"Y") Datedif isn't listed in the function wizard, but can be found in the help menu. Dan E "Michelle" <merri026@mc.duke.edu> wrote in message news:047301c389ea$e6638420$a301280a@phx.gbl... > What formula do you use to change a birthdate to find the > age of person As Mr. McGimpsey just pointed out in response to another one of my posts... It's only in the help menu in 2000 (possibly 2003/XP) For all other XL versi...

Font in Formula Bar
Greetings... I just started using Microsoft 2003. In Excel, I can't read text that is in the Formula Bar because it is i a weird small font. I looked everywhere to figure out how to change th font in the Formula Bar, but i can't find it anywhere. What font does it use? Is it based on an option in Microsoft or is i based on something to do with the Desktop settings...i've looke everywhere for an answer to this. Thanks for any help. jdkuhndo -- jdkuhndo ----------------------------------------------------------------------- jdkuhndog's Profile: http://www.excelforum.com...

Colour numbers within my formulae
I have been playing around with the '&' function, allowing me to mix text with calculations and cell references. I have the following at the moment: ="Of the "&TEXT(J954,"�#,0.00")&" made this year, I have managed to save "&TEXT(100/J954*J955, "#,#0.0")&"%" Which gives me a cell displaying the following: Of the �0.00 made this year, I have managed to save 0.0% What I would like to do is show the numbers (�0.00; 0.0%) in the colour blue. I don't want to add conditional formatting, just apply a colour so t...

protecting formulas
I am trying to find a way to protect formulas on a spreadsheet whil still allowing users to put in new data. Is there a way to do this? am using excel 200 -- Message posted from http://www.ExcelForum.com Cherilyn unlock the cells where you want input and then protect the sheet with a password. Format | Cells... | Protection tab | Locked = false (not ticked) Regards Trevor "Cherilyn >" <<Cherilyn.16krpe@excelforum-nospam.com> wrote in message news:Cherilyn.16krpe@excelforum-nospam.com... > I am trying to find a way to protect formulas on a spreadsheet while ...

using ROW in a formula
morning, i have a formula which uses cell "J6" for example... as i drag down it changes to "J7" "J8" "J9" etc... etc... however, this formula is linked to a user form, and as the user clicks 'OK' it copy's the formula into my specified cell... however it copy's the exact contents of the formula, and it does not change the row.. so each formula down the sheet uses "J6". i want to modify the formula so it uses "J&ROW()" for example, but i do not know how to write this into a formula; i get the #NAME? error. any...

DST issue
I entered the catalog item: <Feature Name="Item" CatalogItemId="Item:MS:sysgen_timesvc_dst" /> and confirmed that the device has the registry key: HKLM\Software\Microsoft\Clock\AutoDST = 1 But the device still does not automatically adjust for DST. I set the timezone to Pacific, set the time to 3/14/2010 at 1:59AM, waited for one minute...and the time changed to 2:00AM instead of 3:00AM. What am I missing? Thanks. which OS version are you using? have you updated it with the relevant qfes which deal with the changing in DST starting day? -...

Help with some array formulas
Hi there I have this scenario Tariff A 100 mins Tariff B 200 mins Tariff C 50 mins Tariff A 125 mins Tariff C 150 mins Tariff B 175 mins I want to extract the following information: Tariff A 0-50 mins 0 51-100 mins 1 101-150 mins 1 Tariff B .... 150-200 mins 2 Etc etc I know this has to be done by an array... anyone can help me out please... please also guide me as to how the array is created (i.e. what each part of the formula represents) Thanks in advance for your support Much appreciated Brian how about non-ar...

A challenge #2
Hi all you masterminds, I've spent some time trying to solve this problem. Not exactl because I need this, but because it's challenging. At least to me i is. So, can you make a worksheet function that reverses a string? That' it basically. - Asse -- Message posted from http://www.ExcelForum.com Yes, something like this. I haven't tried to code but.. Dim variable as string, Result as string Dim Length as integer, x as integer Length = len(variable) x=Length while x<>x-Length Result = Result & mid(variable,x,1) x=x-1 wend msgbox Result Cheers Paa -- Message ...

help with formula #8
I have a spread sheet like this. A1 is a # for Jan 2004, A2 Feb. 2004 down to dec in A12, Column B is the same but for 2005. row B13 is a YTD for 2005. I need a formula that will automatically count just the same YTD period of 2004 in A13. My first thought was just average per month and multiply it by the number of the month we are in but is was not accurate enough for me due to slow time vs. the busy times. I have many columns to do so I need something fast. Is it possible to have a cell set up where I could just input a number (say 4 for April) and then the formula would only count...

rollout e2k3, any issues with migrating e2k mailboxes
hi all we plan on installing exchange 2003 soon and i have a few questions and ideas which i welcome any comments. here is our current setup: + small domain (2dcs, 10-15 workstations, windows XP) + we are behind a firewall (cisco pix 501, nat/pat). + dc1: (win2k server,fmso,gc,dns,dhcp,sql server,ras) + dc2: (win2k server,dns,ras,ex2k,IIS,gc) what i am thinking: + new member server (win2k3 server, ex2k3, IIS) <- migrate mailboxes from dc2 + dc2: (clean install win2k3 server,dc,fmso,gc,dns,dhcp,ras,veritas backup) + dc1: (clean install win2k3 server,dc,dns,gc) i think it is preferable ...

2 questions regarding count formula
If I have the following data A B C 1 Personnel group type p't number 2 Melinda 1 4 3 Sean 1 5 4 Melinda 1 6 5 Sean 2 30 6 Fay 3 8 7 Melinda 1 32 1. How shall I contruct my formula to count the number of data that satisfy the following criteria: 1. personnel is Melinda 2. group type is 1 3. ...

Newbie Q
Hi, I am wondering if there is a way to prevent a formula to be updated after an insert. As an example, let's say that I have a cell on Sheet2 that references Sheet1!A4. Let's say that I insert a row in Sheet1 above row 4. Automatically my formula on Sheet2 is updated so that is references Sheet1!A5. Is there a way to prevent this? TIA. Hi, Check out Indirect function =INDIRECT("sheet1!A4") Regards, Hari India "Atchoum" <NO_goglus_JUNK@videotron.ca> wrote in message news:OsZ1d.64670$WH1.1629608@wagner.videotron.net... > Hi, > > I am wonderin...