Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can someone help with this complex formula?
I’m building a general ledger. I’m about 80% complete and need to now move into the final 2 pages (1. YTD Budget Summary which will pull from the 2. roll-up page). I built the following formula; =SUMIF('AP-24X7'!$P:$P,"="&(TEXT(C$5,"mmm-yy")),'SP-24X7'!$L:$L) It seemed to work at first, but I quickly realized that it was not pulling information exclusive to both the date and to mthe object code. It would put the amounts that were exclusive to only the date in all the corresponding month columns across all of the object codes whether it was exclusive to that object code or not. What I'm trying to do is; Build a general ledger with multiple tabs that refer to 14 different cost centers. Each of the cost centers spend from 63 different "Object Codes" for Example 56200. I need to pull together into a roll-up page the summary of all expenses on a monthly basis spent in each object code, by each of the cost centers (A Net Roll-Up). So ultimately I need to construct a formula that will go to all 14 worksheets look for exclusive data that refers to the date and also the object code (ie. 56200), and return the data back to my designated roll-up page. Can someone assist me? Thank you in advance! -- My2Boyz9802 ------------------------------------------------------------------------ My2Boyz9802's Profile: http://www.excelforum.com/member.php...o&userid=33704 View this thread: http://www.excelforum.com/showthread...hreadid=534954 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can someone help with this complex formula?
See previous post.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "My2Boyz9802" wrote in message ... I'm building a general ledger. I'm about 80% complete and need to now move into the final 2 pages (1. YTD Budget Summary which will pull from the 2. roll-up page). I built the following formula; =SUMIF('AP-24X7'!$P:$P,"="&(TEXT(C$5,"mmm-yy")),'SP-24X7'!$L:$L) It seemed to work at first, but I quickly realized that it was not pulling information exclusive to both the date and to mthe object code. It would put the amounts that were exclusive to only the date in all the corresponding month columns across all of the object codes whether it was exclusive to that object code or not. What I'm trying to do is; Build a general ledger with multiple tabs that refer to 14 different cost centers. Each of the cost centers spend from 63 different "Object Codes" for Example 56200. I need to pull together into a roll-up page the summary of all expenses on a monthly basis spent in each object code, by each of the cost centers (A Net Roll-Up). So ultimately I need to construct a formula that will go to all 14 worksheets look for exclusive data that refers to the date and also the object code (ie. 56200), and return the data back to my designated roll-up page. Can someone assist me? Thank you in advance! -- My2Boyz9802 ------------------------------------------------------------------------ My2Boyz9802's Profile: http://www.excelforum.com/member.php...o&userid=33704 View this thread: http://www.excelforum.com/showthread...hreadid=534954 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can someone help with this complex formula?
Sorry about not including that in my new thread. I just figured I had royally confused everyone, and needed to start from scratch. I tried your formula, it didn't work...I tried to finagle it that didn't work either, so ultimately I was looking for another angle. Do you think I need to build a pivot table? If yes, I have no clue on how to do that. I've been working with Excel for well over 10 years and this task makes me feel as though I've never looked at this program a day in my life?? -- My2Boyz9802 ------------------------------------------------------------------------ My2Boyz9802's Profile: http://www.excelforum.com/member.php...o&userid=33704 View this thread: http://www.excelforum.com/showthread...hreadid=534954 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can someone help with this complex formula?
Maybe, but you will need to create a helper column to get the date part that
you are seeking. Pivot tables - See http://www.contextures.com/xlPivot01.html and http://www.peltierteh.com/Excel/Pivots/pivottables.htm -- HTH Bob Phillips (remove nothere from email address if mailing direct) "My2Boyz9802" wrote in message ... Sorry about not including that in my new thread. I just figured I had royally confused everyone, and needed to start from scratch. I tried your formula, it didn't work...I tried to finagle it that didn't work either, so ultimately I was looking for another angle. Do you think I need to build a pivot table? If yes, I have no clue on how to do that. I've been working with Excel for well over 10 years and this task makes me feel as though I've never looked at this program a day in my life?? -- My2Boyz9802 ------------------------------------------------------------------------ My2Boyz9802's Profile: http://www.excelforum.com/member.php...o&userid=33704 View this thread: http://www.excelforum.com/showthread...hreadid=534954 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can someone help with this complex formula?
Your help is profoundly appreciated! Candace -- My2Boyz9802 ------------------------------------------------------------------------ My2Boyz9802's Profile: http://www.excelforum.com/member.php...o&userid=33704 View this thread: http://www.excelforum.com/showthread...hreadid=534954 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Complex formula | Excel Discussion (Misc queries) |