The solution I offered was based on your sheets being named Sheet1,
Sheet2, Sheet3, etc. Since your sheets are actually named differently,
you'll have to use the method outlined by Biff.

Nevertheless, depending on how your sheets are actually named, it may
still be possible to dispense with having to create a list of sheet
names for the formula to reference.

In article ,
"Noemi" wrote:

Hi Domenic
Can you please confirm that the "Sheet" is meant to be the name of the
actual sheets from 2 to 20...if they are how do I refer to them when they
dont have the same name..

Thanks Noemi

"Domenic" wrote:

Try...

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("2 :20"))&"!A1:A100"),Shee
t1!A1,INDIRECT("Sheet"&ROW(INDIRECT("2:20"))&"!E1: E100")))

Note that ROW(INDIRECT("2:20")) refers to Sheet2 through Sheet20.
Change this reference accordingly.

Hope this helps!

In article ,
"Noemi" wrote:

I have a workbook which contains about 20 worksheets.

In sheet1 I have a list of numbers which could be also on the other 19
sheets.

What I need to do is look at each of the other 19 sheets to see if they
have
the number which is on sheet1 and if so I need to get the numerical data
in
the 5th column to be returned and added together.

ie
sheet1 a1 = 2001

sheet4 a5 = 2001 e5 = 2
sheet6 a21 = 2001 e21 = 5

therefore sheet1 b1 = e5 + e21 = 7

Hopefully someone could help me.

Thanks
Noemi