Is lookup the key?

[anieuwoudt]

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

 

[Domenic]

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!

 

[anieuwoudt]

Surely there must be an easier way? Perhaps by using more than on
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,'Jun
2005'!B5:B53,'June 2005'!E5:E53), ...etc )

where 1001 is the employee number, May 2005 is the sheet name, B colum
is where the employee number is stored and E column is where the dolla
values are stored.

When it couldn't find 1001 in the first sheet (because 1001 did no
place any orders in May), it gave an -#NA-. It's important that I lis
every single employee in the final totals sheet, even if the solutio
to my problem doesnt involve calculating how much every single employe
spent each and every month

 

[Domenic]

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 2005ecember 2005'!$B$5:$B$53)=1001),
THREED('January 2005ecember 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(2005,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!

 

[anieuwoudt]

Morefunc Add-in has done it!

Thanks for the help.

Kind Regards,

A. Nieuwoudt.