Lookup
 

I have a workbook that contains 27 worksheets with 20 columns and 120 rows
each. Column D in each worksheet contains a different name in each row and
Column L in each worksheet contains a different # assosiated with the name in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum of
all #'s from Column L assosiated with that name from all worksheets. The
names are all in different rows in each worksheet.

Please help!

Lookup
 

Do the sheet names follow some sequential pattern? Like Week1, Week2, Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and 120 rows
each. Column D in each worksheet contains a different name in each row
and
Column L in each worksheet contains a different # assosiated with the name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum of
all #'s from Column L assosiated with that name from all worksheets. The
names are all in different rows in each worksheet.

Please help!

Lookup
 

Did the answer I gave you yesterday not work?

Pete

On Dec 7, 6:40 pm, smooney wrote:
I have a workbook that contains 27 worksheets with 20 columns and 120 rows
each. Column D in each worksheet contains a different name in each row and
Column L in each worksheet contains a different # assosiated with the name in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum of
all #'s from Column L assosiated with that name from all worksheets. The
names are all in different rows in each worksheet.

Please help!

Lookup
 

Yes...07PP01, 07PP02, 07PP03....etc.

"T. Valko" wrote:

Do the sheet names follow some sequential pattern? Like Week1, Week2, Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and 120 rows
each. Column D in each worksheet contains a different name in each row
and
Column L in each worksheet contains a different # assosiated with the name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum of
all #'s from Column L assosiated with that name from all worksheets. The
names are all in different rows in each worksheet.

Please help!

Lookup
 

No it didn't. I played around with it for a long time and it kept returning
a value of zero. Thought I would start fresh again with more details to the
quetion. Thanks anyway.

"Pete_UK" wrote:

Did the answer I gave you yesterday not work?

Pete

On Dec 7, 6:40 pm, smooney wrote:
I have a workbook that contains 27 worksheets with 20 columns and 120 rows
each. Column D in each worksheet contains a different name in each row and
Column L in each worksheet contains a different # assosiated with the name in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum of
all #'s from Column L assosiated with that name from all worksheets. The
names are all in different rows in each worksheet.

Please help!

Lookup
 

Ok...

Assuming the sheet names a 07PP01, 07PP02 ... 07PP27

=SUMPRODUCT(SUMIF(INDIRECT("07PP"&TEXT(ROW(INDIREC T("1:27")),"00")&"!D:D"),A2,INDIRECT("07PP"&TEXT(R OW(INDIRECT("1:27")),"00")&"!L:L")))

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Yes...07PP01, 07PP02, 07PP03....etc.

"T. Valko" wrote:

Do the sheet names follow some sequential pattern? Like Week1, Week2,
Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and 120
rows
each. Column D in each worksheet contains a different name in each row
and
Column L in each worksheet contains a different # assosiated with the
name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum
of
all #'s from Column L assosiated with that name from all worksheets.
The
names are all in different rows in each worksheet.

Please help!

Lookup
 

In the formula, A2 is the name of interest!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

Assuming the sheet names a 07PP01, 07PP02 ... 07PP27

=SUMPRODUCT(SUMIF(INDIRECT("07PP"&TEXT(ROW(INDIREC T("1:27")),"00")&"!D:D"),A2,INDIRECT("07PP"&TEXT(R OW(INDIRECT("1:27")),"00")&"!L:L")))

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Yes...07PP01, 07PP02, 07PP03....etc.

"T. Valko" wrote:

Do the sheet names follow some sequential pattern? Like Week1, Week2,
Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and 120
rows
each. Column D in each worksheet contains a different name in each
row
and
Column L in each worksheet contains a different # assosiated with the
name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum
of
all #'s from Column L assosiated with that name from all worksheets.
The
names are all in different rows in each worksheet.

Please help!

Lookup
 

Thank you...but it's not working. What should I replace the A2 with in the
formula? The person's name? Without changing the A2, the formula is
returning a value of zero. Should I be entering this formula in a certain
cell in the 'Totals' worksheet?

Thank you for your patience!

"T. Valko" wrote:

In the formula, A2 is the name of interest!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

Assuming the sheet names a 07PP01, 07PP02 ... 07PP27

=SUMPRODUCT(SUMIF(INDIRECT("07PP"&TEXT(ROW(INDIREC T("1:27")),"00")&"!D:D"),A2,INDIRECT("07PP"&TEXT(R OW(INDIRECT("1:27")),"00")&"!L:L")))

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Yes...07PP01, 07PP02, 07PP03....etc.

"T. Valko" wrote:

Do the sheet names follow some sequential pattern? Like Week1, Week2,
Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and 120
rows
each. Column D in each worksheet contains a different name in each
row
and
Column L in each worksheet contains a different # assosiated with the
name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the sum
of
all #'s from Column L assosiated with that name from all worksheets.
The
names are all in different rows in each worksheet.

Please help!

Lookup
 

Here's a small sample file that demonstrates this:

sumif.xls 14kb

http://cjoint.com/?mhvzHn7rJH

I'm only using 3 sheets in this sample so you'll notice I use this in the
formula:

....ROW(INDIRECT("1:3"))...

If you're using 27 sheets then it would be:

....ROW(INDIRECT("1:27"))...

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Thank you...but it's not working. What should I replace the A2 with in
the
formula? The person's name? Without changing the A2, the formula is
returning a value of zero. Should I be entering this formula in a certain
cell in the 'Totals' worksheet?

Thank you for your patience!

"T. Valko" wrote:

In the formula, A2 is the name of interest!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

Assuming the sheet names a 07PP01, 07PP02 ... 07PP27

=SUMPRODUCT(SUMIF(INDIRECT("07PP"&TEXT(ROW(INDIREC T("1:27")),"00")&"!D:D"),A2,INDIRECT("07PP"&TEXT(R OW(INDIRECT("1:27")),"00")&"!L:L")))

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Yes...07PP01, 07PP02, 07PP03....etc.

"T. Valko" wrote:

Do the sheet names follow some sequential pattern? Like Week1, Week2,
Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and
120
rows
each. Column D in each worksheet contains a different name in each
row
and
Column L in each worksheet contains a different # assosiated with
the
name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the
sum
of
all #'s from Column L assosiated with that name from all
worksheets.
The
names are all in different rows in each worksheet.

Please help!

Lookup
 

Yes...that works! Thank you....thank you...thank you!

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sumif.xls 14kb

http://cjoint.com/?mhvzHn7rJH

I'm only using 3 sheets in this sample so you'll notice I use this in the
formula:

....ROW(INDIRECT("1:3"))...

If you're using 27 sheets then it would be:

....ROW(INDIRECT("1:27"))...

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Thank you...but it's not working. What should I replace the A2 with in
the
formula? The person's name? Without changing the A2, the formula is
returning a value of zero. Should I be entering this formula in a certain
cell in the 'Totals' worksheet?

Thank you for your patience!

"T. Valko" wrote:

In the formula, A2 is the name of interest!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

Assuming the sheet names a 07PP01, 07PP02 ... 07PP27

=SUMPRODUCT(SUMIF(INDIRECT("07PP"&TEXT(ROW(INDIREC T("1:27")),"00")&"!D:D"),A2,INDIRECT("07PP"&TEXT(R OW(INDIRECT("1:27")),"00")&"!L:L")))

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Yes...07PP01, 07PP02, 07PP03....etc.

"T. Valko" wrote:

Do the sheet names follow some sequential pattern? Like Week1, Week2,
Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and
120
rows
each. Column D in each worksheet contains a different name in each
row
and
Column L in each worksheet contains a different # assosiated with
the
name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and the
sum
of
all #'s from Column L assosiated with that name from all
worksheets.
The
names are all in different rows in each worksheet.

Please help!

Lookup
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Yes...that works! Thank you....thank you...thank you!

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sumif.xls 14kb

http://cjoint.com/?mhvzHn7rJH

I'm only using 3 sheets in this sample so you'll notice I use this in the
formula:

....ROW(INDIRECT("1:3"))...

If you're using 27 sheets then it would be:

....ROW(INDIRECT("1:27"))...

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Thank you...but it's not working. What should I replace the A2 with in
the
formula? The person's name? Without changing the A2, the formula is
returning a value of zero. Should I be entering this formula in a
certain
cell in the 'Totals' worksheet?

Thank you for your patience!

"T. Valko" wrote:

In the formula, A2 is the name of interest!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

Assuming the sheet names a 07PP01, 07PP02 ... 07PP27

=SUMPRODUCT(SUMIF(INDIRECT("07PP"&TEXT(ROW(INDIREC T("1:27")),"00")&"!D:D"),A2,INDIRECT("07PP"&TEXT(R OW(INDIRECT("1:27")),"00")&"!L:L")))

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
Yes...07PP01, 07PP02, 07PP03....etc.

"T. Valko" wrote:

Do the sheet names follow some sequential pattern? Like Week1,
Week2,
Week3
etc?

--
Biff
Microsoft Excel MVP


"smooney" wrote in message
...
I have a workbook that contains 27 worksheets with 20 columns and
120
rows
each. Column D in each worksheet contains a different name in
each
row
and
Column L in each worksheet contains a different # assosiated
with
the
name
in
Column D.

On a separate 'Totals' worksheet, I need the person's name and
the
sum
of
all #'s from Column L assosiated with that name from all
worksheets.
The
names are all in different rows in each worksheet.

Please help!