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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[anieuwoudt]

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.

[Domenic]

Surely there must be an easier way?

If you download the Morefunc.xll 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(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.

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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

[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!

In article <[email protected]>,
anieuwoudt <[email protected]>
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