| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
add values using vlookup over multi sheets
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 |
|
#2
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#3
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#4
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#5
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#6
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#7
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#8
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#9
|
|||
|
|||
|
add values using vlookup over multi sheets
|
|
#11
|
|||
|
|||
|
add values using vlookup over multi sheets
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 |
|
#12
|
|||
|
|||
|
add values using vlookup over multi sheets
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 as pulling matching info from various sheets onto the master one, but i don't need the information to be added with other cells when the same number appears because the number is unique. ex: column a on the master sheet ('Master') is always a unique number, and that number also appears only once on one of the sheets 2-50 (i defined a name for the range or sheets, called 'subs') if Master, A2= 17, then when 17 appears in column A on one of the other sheets in range 'subs', I want Master D2 to show the information from 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 in column A on sheets2-50, and insert the info from column E(whatever row 17 is found in) into Master, D2. Please help. -- Dahaynes ------------------------------------------------------------------------ Dahaynes's Profile: http://www.excelforum.com/member.php...o&userid=28276 View this thread: http://www.excelforum.com/showthread...hreadid=477384 |
|
#13
|
|||
|
|||
|
add values using vlookup over multi sheets
Try...
=SUMPRODUCT(SUMIF(INDIRECT("'"&Subs&"'!A2:A100"),M aster!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! In article , Dahaynes wrote: 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 as pulling matching info from various sheets onto the master one, but i don't need the information to be added with other cells when the same number appears because the number is unique. ex: column a on the master sheet ('Master') is always a unique number, and that number also appears only once on one of the sheets 2-50 (i defined a name for the range or sheets, called 'subs') if Master, A2= 17, then when 17 appears in column A on one of the other sheets in range 'subs', I want Master D2 to show the information from 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 in column A on sheets2-50, and insert the info from column E(whatever row 17 is found in) into Master, D2. Please help. |
|
#14
|
|||
|
|||
|
add values using vlookup over multi sheets
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'? -- Dahaynes ------------------------------------------------------------------------ Dahaynes's Profile: http://www.excelforum.com/member.php...o&userid=28276 View this thread: http://www.excelforum.com/showthread...hreadid=477384 |
|
#15
|
|||
|
|||
|
add values using vlookup over multi sheets
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? In article , Dahaynes wrote: 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'? |
|
#16
|
|||
|
|||
|
add values using vlookup over multi sheets
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!!!! -- Dahaynes ------------------------------------------------------------------------ Dahaynes's Profile: http://www.excelforum.com/member.php...o&userid=28276 View this thread: http://www.excelforum.com/showthread...hreadid=477384 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup returning multiple values | Excel Worksheet Functions | |||
| Adjusting Vlookup Values | Excel Worksheet Functions | |||
| VLOOKUP to search multiple values? | Excel Worksheet Functions | |||
| vlookup only a set of values. | Excel Worksheet Functions | |||
| VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions | |||
Linear Mode
