Lookup

[smooney]

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!

 

[T. Valko]

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

 

[Pete_UK]

Did the answer I gave you yesterday not work?

Pete

 

[smooney]

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

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

 

[smooney]

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.

 

[T. Valko]

Ok...

Assuming the sheet names are: 07PP01, 07PP02 ... 07PP27

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

 

[T. Valko]

In the formula, A2 is the name of interest!

 

[smooney]

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]

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"))...

 

[smooney]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!