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

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

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

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

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