Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is lookup the key?
Hi, What I'm trying to do is hard to explain but quite a simple thing. I have recorded the amount of money spent by employees. The table is set out as follows: Date | Employee No. | Name | Items Purchased | Sub-Total ^ Sorted by Date - **This cannot be changed!** I have a seperate worksheet for each month, and I have one final worksheet where I wish to display how much was spent by EACH employee for the entire year. This needs to be looked up via Employee No I would assume. *Note:* These are seperate worksheets, but all part of the same book. *Note:* Each month has employees, but not all employees spend money each month, and are therefore not listed for that particular month. How can I add the sub-totals of each employee on a seperate row in the final worksheet? This is how I'd prefer my Year Totals worksheet to appear: Employee No. | Name | 2005 Total Spent Any help is appreciated! Regards, A. Nieuwoudt -- anieuwoudt ------------------------------------------------------------------------ anieuwoudt's Profile: http://www.excelforum.com/member.php...o&userid=24147 View this thread: http://www.excelforum.com/showthread...hreadid=377647 |
#2
|
|||
|
|||
Assuming that on your final worksheet A1:A12 contains a list of your
sheet names for each month, and Column B contains a list of 'Employee Numbers', try... C1, copied down: =SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$12&"'!B:B") ,B1,INDIRECT("'"&$A$1:$ A$12&"'!E:E"))) Depending on how you've named your monthly sheets, the formula could be changed to avoid having to maintain a list of sheet names. Hope this helps! In article , anieuwoudt wrote: Hi, What I'm trying to do is hard to explain but quite a simple thing. I have recorded the amount of money spent by employees. The table is set out as follows: Date | Employee No. | Name | Items Purchased | Sub-Total ^ Sorted by Date - **This cannot be changed!** I have a seperate worksheet for each month, and I have one final worksheet where I wish to display how much was spent by EACH employee for the entire year. This needs to be looked up via Employee No I would assume. *Note:* These are seperate worksheets, but all part of the same book. *Note:* Each month has employees, but not all employees spend money each month, and are therefore not listed for that particular month. How can I add the sub-totals of each employee on a seperate row in the final worksheet? This is how I'd prefer my Year Totals worksheet to appear: Employee No. | Name | 2005 Total Spent Any help is appreciated! Regards, A. Nieuwoudt |
#3
|
|||
|
|||
Surely there must be an easier way? Perhaps by using more than one formula? My first attempt at solving the issue was to do this: =SUM(LOOKUP(1001,'May 2005'!B5:B53,'May 2005'!E5:E53),LOOKUP(1001,'June 2005'!B5:B53,'June 2005'!E5:E53), ...etc ) where 1001 is the employee number, May 2005 is the sheet name, B column is where the employee number is stored and E column is where the dollar values are stored. When it couldn't find 1001 in the first sheet (because 1001 did not place any orders in May), it gave an -#NA-. It's important that I list every single employee in the final totals sheet, even if the solution to my problem doesnt involve calculating how much every single employee spent each and every month. -- anieuwoudt ------------------------------------------------------------------------ anieuwoudt's Profile: http://www.excelforum.com/member.php...o&userid=24147 View this thread: http://www.excelforum.com/showthread...hreadid=377647 |
#4
|
|||
|
|||
anieuwoudt Wrote: Surely there must be an easier way? If you download the 'Morefunc.xll' (http://xcell05.free.fr/) add-in, you can use the THREED function... =SUMPRODUCT(--(THREED('January 2005:December 2005'!$B$5:$B$53)=1001), THREED('January 2005:December 2005'!E$5:E$53)) Perhaps by using more than one formula? My first attempt at solving the issue was to do this: =SUM(LOOKUP(1001,'May 2005'!B5:B53,'May 2005'!E5:E53),LOOKUP(1001,'June 2005'!B5:B53,'June 2005'!E5:E53), ...etc ) I'm can't see how this formula is any easier? Maybe something like this... =SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(DATE(2005,ROW( INDIRECT($A$1&":"&$B$1)),1),"mmmm yyyy")&"'!B5:B53"),1001,INDIRECT("'"&TEXT(DATE(200 5,ROW(INDIRECT($A$1&":"&$B$1)),1),"mmmm yyyy")&"'!E5:E53"))) ...where A1 contains the number of the first month of interest, such as 1 for January, and B1 contains the number of the last month of interest, such as 12 for December. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=377647 |
#5
|
|||
|
|||
Morefunc Add-in has done it! Thanks for the help. Kind Regards, A. Nieuwoudt. -- anieuwoudt ------------------------------------------------------------------------ anieuwoudt's Profile: http://www.excelforum.com/member.php...o&userid=24147 View this thread: http://www.excelforum.com/showthread...hreadid=377647 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup more than one cell | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |