add values using vlookup over multi sheets

N

Noemi

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
 
D

Domenic

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!
 
B

Biff

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(INDIRECT("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:

Insert>Name>Define
Name: Snames
Refers to: =Sheet1!$J$1:$J$19

Then:

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

Biff
 
N

Noemi

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
 
N

Noemi

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
 
B

Biff

Hi!

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

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

Insert>Name>Define
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
 
N

Noemi

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
 
B

Biff

Hi!

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

Biff
 
D

Domenic

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.
 
D

Dahaynes

Biff, is it possible to send me a sample file (or even just the formula
of this too? I'm having a similar problem. Same scenario as far a
pulling matching info from various sheets onto the master one, but
don't need the information to be added with other cells when the sam
number appears because the number is unique.

ex:

column a on the master sheet ('Master') is always a unique number, an
that number also appears only once on one of the sheets 2-50 (i define
a name for the range or sheets, called 'subs')

if Master, A2= 17, then when 17 appears in column A on one of the othe
sheets in range 'subs', I want Master D2 to show the information fro
column E of the same row that the 17 appears in.

stated a different way: if Master A2 =17, then i want to find 17 i
column A on sheets2-50, and insert the info from column E(whatever ro
17 is found in) into Master, D2. Please help
 
D

Domenic

Try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&Subs&"'!A2:A100"),Master!A2,INDIRECT("'"&S
ubs&"'!E2:E100")))

Adjust the ranges accordingly. Note that you can also use whole column
references if you want.

Hope this helps!
 
D

Dahaynes

Domenic,

Thank you, that works wonders when it retrieves the data that is
entered into column E, it's perfect. But how do I get D2 to be blank
if there is no data entered in column E, right now it's giving me a
'0'?
 
D

Domenic

One way would be to hide the zero value using custom formatting...

Format > Cells > Number > Custom > Type: 0;-0;;@

Note that the underlying value for the cell will be zero. Will this do?
 
D

Dahaynes

Thanks. I couldn't figure out how to get it to work for my speadsheet,
because I needed the values to be in mm/dd/yy format. However, I also
figured out that I could do it by going to Tools->Options->View, and
deselect the zero values box. Thanks a lot!!!!
 
Top