creating a summary sheet
 

I have multiple sheets.
Each contains sales person's name across top and their commission per month
down the column. each sheet is for a different project.
I need to creat a summary sheet with sales person's name on top and their
total commision from all other sheets per month down the column.
So, I need to lookup sales person's name from the top row, match with month
from the left column and add all corresponding commision figures from a range
of sheets.

Appreciate any help.

Happy New Year to All.

creating a summary sheet
 

The answer could vary depending on a couple factors. Are the sales people's
name always in the same column? Are the months always in the same rows? (i.e.
- Sales Rep A is always in Column B, January is always in row 2.)

If that happens to be the case, it's:
=sum(sheet2!B2,sheet3!B2) etc.

Assuming this is not the case, if the month is always in the same row, but
rep is in different columns:

=sum(HLOOKUP(B1,sheet2!A:B,2,FALSE)+HLOOKUP(B1,she et3!A:B,2,FALSE)) etc.

If month is different, but rep is in the same column:

=sum(VLOOKUP(A2,sheet2!A:B,2,FALSE)+HLOOKUP(A2,she et3!A:B,2,FALSE)) etc.

If neither, I'm not sure, but someone else can probably give that...

"AM" wrote:

I have multiple sheets.
Each contains sales person's name across top and their commission per month
down the column. each sheet is for a different project.
I need to creat a summary sheet with sales person's name on top and their
total commision from all other sheets per month down the column.
So, I need to lookup sales person's name from the top row, match with month
from the left column and add all corresponding commision figures from a range
of sheets.

Appreciate any help.

Happy New Year to All.

creating a summary sheet
 

Thanks for your response.
You are correct. Sales person's name and /or Month are not always in same
relative position.
That's why my question was to look up name across top AND month on the left
column. Whenever there is a match(i.e. Tom / March) that value should be
added to other such matches from all other sheets.
It may require some combination of ARRAY, LOOKUP and Conditional functions.

Also, Your suggestion would work if I keep the months in same ROWs. But
isn't there an easier way to create formulas without repeating.
i.e.
=sum(VLOOKUP(A2,sheet2!A:B,2,FALSE)+HLOOKUP(A2,she et3!A:B,2,FALSE)) etc.
In your suggestion If I have 50 sheets, do I have to write a long expression
with 50 such entries?
Again, Thanks for your help.
AM




"Sean Timmons" wrote:

The answer could vary depending on a couple factors. Are the sales people's
name always in the same column? Are the months always in the same rows? (i.e.
- Sales Rep A is always in Column B, January is always in row 2.)

If that happens to be the case, it's:
=sum(sheet2!B2,sheet3!B2) etc.

Assuming this is not the case, if the month is always in the same row, but
rep is in different columns:

=sum(HLOOKUP(B1,sheet2!A:B,2,FALSE)+HLOOKUP(B1,she et3!A:B,2,FALSE)) etc.

If month is different, but rep is in the same column:

=sum(VLOOKUP(A2,sheet2!A:B,2,FALSE)+HLOOKUP(A2,she et3!A:B,2,FALSE)) etc.

If neither, I'm not sure, but someone else can probably give that...

"AM" wrote:

I have multiple sheets.
Each contains sales person's name across top and their commission per month
down the column. each sheet is for a different project.
I need to creat a summary sheet with sales person's name on top and their
total commision from all other sheets per month down the column.
So, I need to lookup sales person's name from the top row, match with month
from the left column and add all corresponding commision figures from a range
of sheets.

Appreciate any help.

Happy New Year to All.

creating a summary sheet
 

Try this:

Let's assume:

Bob Jack Bill
January
February
March

=VLOOKUP($A2,Sheet2!$1:$4,MATCH(B$1,Sheet2!$1:$1,0 ),FALSE)

This assumes sheet 2 is the name of your filled-in sheet. It also assumes
the names are in row 1, and there are 4 rows.

For more rows, extend $4 to whatever number of rows you need.

This calculates against one sheet. To calculate against a second, just start
with =SUM( then put the formula, then + and paste the formula again, changing
sheet names.

I don't know that there's any way to do this without having to add each
sheet one at a time...

"AM" wrote:

Thanks for your response.
You are correct. Sales person's name and /or Month are not always in same
relative position.
That's why my question was to look up name across top AND month on the left
column. Whenever there is a match(i.e. Tom / March) that value should be
added to other such matches from all other sheets.
It may require some combination of ARRAY, LOOKUP and Conditional functions.

Also, Your suggestion would work if I keep the months in same ROWs. But
isn't there an easier way to create formulas without repeating.
i.e.
=sum(VLOOKUP(A2,sheet2!A:B,2,FALSE)+HLOOKUP(A2,she et3!A:B,2,FALSE)) etc.
In your suggestion If I have 50 sheets, do I have to write a long expression
with 50 such entries?
Again, Thanks for your help.
AM




"Sean Timmons" wrote:

The answer could vary depending on a couple factors. Are the sales people's
name always in the same column? Are the months always in the same rows? (i.e.
- Sales Rep A is always in Column B, January is always in row 2.)

If that happens to be the case, it's:
=sum(sheet2!B2,sheet3!B2) etc.

Assuming this is not the case, if the month is always in the same row, but
rep is in different columns:

=sum(HLOOKUP(B1,sheet2!A:B,2,FALSE)+HLOOKUP(B1,she et3!A:B,2,FALSE)) etc.

If month is different, but rep is in the same column:

=sum(VLOOKUP(A2,sheet2!A:B,2,FALSE)+HLOOKUP(A2,she et3!A:B,2,FALSE)) etc.

If neither, I'm not sure, but someone else can probably give that...

"AM" wrote:

I have multiple sheets.
Each contains sales person's name across top and their commission per month
down the column. each sheet is for a different project.
I need to creat a summary sheet with sales person's name on top and their
total commision from all other sheets per month down the column.
So, I need to lookup sales person's name from the top row, match with month
from the left column and add all corresponding commision figures from a range
of sheets.

Appreciate any help.

Happy New Year to All.