Hi!

Would you like to see a sample file? If so, just let me know where to send
it.

Biff

"Noemi" wrote in message
...
Hi Biff
I have done this (refer below) however I am getting a #REF! error now.

I dont understand what I am doing wrong

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:E180&"'!A2:A180" ),A3,INDIRECT("'"&A2:E180&"'!E2:E180")))


A2:A180 - is the range which contains the data for each worksheet except
worksheet1 which is different from the rest. I dont think I can define the
names for each worksheet as there is about 50 of them.

thanks
Noemi

"Biff" wrote:

Hi!

Make a list of the sheet names in a range of cells, say, J1:J19

Now, give that range a name......

InsertNameDefine
Name = Snames
Refers to: ="whatever sheet this list is on"!$J$1:$J$19

OR, just follow my other example where you refer to the range instead of
using a named range.

Biff

"Noemi" wrote in message
...
Hi Biff

My worksheets have numbers as names therefore I defined then as
follows:

snames = '123456:987654!$A$2:$A$180

can you please confirm this is correct as I am geeting the folloiwng
error

#value!

Thanks Noemi

"Biff" wrote:

Hi!

Try this:

If your sheet names really are Sheet1, Sheet2, Sheet3, etc.,
...........

This is for Sheet2:Sheet20 .........

=SUMPRODUCT(SUMIF(INDIRECT("'sheet"&ROW(INDIRECT(" 2:20"))&"'!A1:A25"),A1,INDIRECT("'sheet"&ROW(INDIR ECT("2:20"))&"'!E1:E25")))

This is equivalent to using a Sumif on each sheet:

=SUMIF(Sheet2!A1:A25,A1,Sheet2!E1:E25)

If your sheet names are something else then:

Create a list of those names. Assume that list is in the range J1:J19.
Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J19&"'!A1:A25"), A1,INDIRECT("'"&J1:J19&"'!E1:E25")))

Or, give the list of sheet names a defined name:

InsertNameDefine
Name: Snames
Refers to: =Sheet1!$J$1:$J$19

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1:A25"), A1,INDIRECT("'"&Snames&"'!E1:E25")))

Biff

"Noemi" wrote in message
...
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